Hi Team,
Well i would like to partition my table on a column with a timestamp datatype, but unfortunately when i try to create a partion am getting the below error,
Error report -
ORA-14074: partition bound must collate higher than that of the last partition
14074. 00000 - "partition bound must collate higher than that of the last partition"
*Cause: Partition bound specified in ALTER TABLE ADD PARTITION
statement did not collate higher than that of the table's
last partition, which is illegal.
*Action: Ensure that the partition bound of the partition to be added
collates higher than that of the table's last partition.
i checked for the high value like
see my scripst for creating table and partitions
CREATE TABLE "RAW_HUAWEI_MSC_OUT_GW_REC1"
(
"FILENAME" VARCHAR2(255 BYTE),
"RECORDNAME" VARCHAR2(255 BYTE),
"RECORDTYPE" VARCHAR2(255 BYTE),
"CALLINGNUMBER" VARCHAR2(255 BYTE),
"CALLEDNUMBER" VARCHAR2(255 BYTE),
"RECORDINGENTITY" VARCHAR2(255 BYTE),
"MSCINCOMINGROUTE" VARCHAR2(255 BYTE),
"MSCOUTGOINGROUTE" VARCHAR2(255 BYTE),
"SETUPTIME" TIMESTAMP (6),
"SEIZURETIME" TIMESTAMP (6),
"ALERTTIME" TIMESTAMP (6),
"ANSWERTIME" TIMESTAMP (6),
"RELEASETIME" TIMESTAMP (6),
"CALLDURATION" VARCHAR2(255 BYTE),
"CAUSEFORTERM" VARCHAR2(255 BYTE),
"DIAGNOSTICS" VARCHAR2(255 BYTE),
"CALLREFERENCE" VARCHAR2(255 BYTE),
"sequenceNumber" VARCHAR2(255 BYTE),
"recordExtensions" VARCHAR2(255 BYTE),
"partialRecordType" VARCHAR2(255 BYTE),
"basicService" VARCHAR2(255 BYTE),
"additionalChgInfo" VARCHAR2(255 BYTE),
"chargedParty" VARCHAR2(255 BYTE),
"originalCalledNumber" VARCHAR2(255 BYTE),
"chargeAreaCode" VARCHAR2(255 BYTE),
"rateIndication" VARCHAR2(255 BYTE),
"roamingNumber" VARCHAR2(255 BYTE),
"mscOutgoingCircuit" VARCHAR2(255 BYTE),
"orgMSCId" VARCHAR2(255 BYTE),
"callEmlppPriority" VARCHAR2(255 BYTE),
"eaSubscriberInfo" VARCHAR2(255 BYTE),
"selectedCIC" VARCHAR2(255 BYTE),
"cUGOutgoingAccessIndicator" VARCHAR2(255 BYTE),
"cUGInterlockCode" VARCHAR2(255 BYTE),
"cUGIncomingAccessUsed" VARCHAR2(255 BYTE),
"mscIncomingRouteAttribute" VARCHAR2(255 BYTE),
"mscOutgoingRouteAttribute" VARCHAR2(255 BYTE),
"networkCallReference" VARCHAR2(255 BYTE),
"voiceIndicator" VARCHAR2(255 BYTE),
"bCategory" VARCHAR2(255 BYTE),
"callType" VARCHAR2(255 BYTE),
"networkOperatorId" VARCHAR2(255 BYTE),
"audioDataType" VARCHAR2(255 BYTE),
"recordNumber" VARCHAR2(255 BYTE),
"translatedNumber" VARCHAR2(255 BYTE),
"location" VARCHAR2(255 BYTE),
"changeOfLocation" VARCHAR2(255 BYTE),
"POPULATIONDATE" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_RAN_ETL_DATA_TBS"
PARTITION BY RANGE ("ANSWERTIME")
(PARTITION "OUT_GW_START" VALUES LESS THAN (TIMESTAMP' 2020-11-05 23:59:59')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_RAN_ETL_DATA_TBS" NOCOMPRESS);
and script for creating a partion
set echo off
set serveroutput on
set trimspool on
set colsep ';'
set numformat 9999999999999999.99
column tm new_value file_time noprint
select to_char(sysdate, 'YYYYMMDD') tm from dual ;
--spool /home/oracle/scripts/partitionlogs/RAW_HUAWEI_MSC_OUT_GW_REC.&file_time..log
DECLARE
partition_name VARCHAR2(30) :=to_char(SYSDATE+10
, 'yyyy_mm');
partition_date VARCHAR2(30) :=to_char(SYSDATE+10, 'yyyy-mm');
BEGIN
EXECUTE IMMEDIATE 'alter table RAW_HUAWEI_MSC_OUT_GW_REC1 add PARTITION "OUT_GW_'||partition_name||'01" VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-01 23:59:59'')
PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS';
END;
/
Your help will be appreciated