Creating a partion table from a non-partitioned table
Posted on 2008-10-16
I have a very large tables that I need to delete rows from. Doing it via code takes 20-30 hours so I've been looking at alternate ways of doing this. I considered creating a partitioned table and truncating the partition I don't want, it's this I'm having problems with. Code I'm using is :-
CREATE TABLE ci_nt_up_part
NT_UP_ID NUMBER(12) NOT NULL,
NT_XID_CD CHAR(30 BYTE) DEFAULT ' ' NOT NULL,
NT_UP_XTYPE_CD CHAR(30 BYTE) DEFAULT ' ' NOT NULL,
NT_UP_STAT_FLG CHAR(2 BYTE) DEFAULT ' ' NOT NULL,
NT_UP_EXTSN_FLG CHAR(4 BYTE) DEFAULT ' ' NOT NULL,
VERSION NUMBER(5) DEFAULT 1 NOT NULL
PARTITION BY RANGE (cre_dttm)
PARTITION remove_p1 VALUES LESS THAN (TO_DATE('16/10/2007:12:00:00','DD/MM/YYYY:HH24:MI:SS')),
PARTITION keep_p2 VALUES LESS THAN (TO_DATE('16/10/2009:12:00:00','DD/MM/YYYY:HH24:MI:SS'))
CREATE TABLE dummy_y as SELECT * FROM ci_nt_up WHERE cre_dttm < ADD_MONTHS(sysdate, - 12);
CREATE TABLE dummy_z as SELECT * FROM ci_nt_up WHERE cre_dttm >= ADD_MONTHS(sysdate, -12);
ALTER TABLE ci_nt_up_part EXCHANGE PARTITION remove_p1 WITH TABLE dummy_y;
ALTER TABLE ci_nt_up_part EXCHANGE PARTITION keep_p2 WITH TABLE dummy_z;
ALTER TABLE ci_nt_up_part
TRUNCATE PARTITION remove_p1;
When I try to run the 1st ALTER TABLE statement I get the error
"ORA-14099: all rows in table do not qualify for specified partition".
The cre_dttm field is date and time. I've tried to add the time into the partition, truncate the dates but nothing seems to work.
Any suggestions as to where I'm going wrong ??
p.s. the 2nd ALTER TABLE statement works fine.