Partitioning a table from a normal table:


Original table: PX_REFERENCE


CREATE TABLE PX_REFERENCE_PART1
(
CREATEDATE DATE DEFAULT SYSDATE NOT NULL,
....
)
PARTITION BY RANGE (CREATEDATE)
(PARTITION PX_REFERENCE_2010 VALUES LESS THAN (TO_TIMESTAMP('17-JUN-2011 00:00:00','DD-MON-YYYY HH24:MI:SS')),
PARTITION PX_REFERENCE_2011 VALUES LESS THAN (TO_TIMESTAMP('01-JAN-2012 00:00:00','DD-MON-YYYY HH24:MI:SS')),
PARTITION PX_REFERENCE_2012 VALUES LESS THAN (TO_TIMESTAMP('01-JAN-2013 00:00:00','DD-MON-YYYY HH24:MI:SS')),
PARTITION PX_REFERENCE_2013 VALUES LESS THAN (TO_TIMESTAMP('01-JAN-2014 00:00:00','DD-MON-YYYY HH24:MI:SS')),
PARTITION PX_REFERENCE_2014 VALUES LESS THAN (TO_TIMESTAMP('01-JAN-2015 00:00:00','DD-MON-YYYY HH24:MI:SS'))
)
;


CREATE INDEX INDEX_REF_ID1 ON PX_REFERENCE_PART1 .....
CREATE INDEX INDEX_REF_ID2 ON PX_REFERENCE_PART1 .....










------------


alter table PX_REFERENCE_PART1 exchange partition PX_REFERENCE_2010 with table PX_REFERENCE;
alter table PX_REFERENCE rename to PX_REFERENCE_OLD1;
alter table PX_REFERENCE_PART1 rename to PX_REFERENCE;
drop table PX_REFERENCE_OLD1;



-- Rebuild indexes:

select 'alter index '||index_name||' rebuild tablespace PEGA55_IND;' from user_indexes where status='UNUSABLE';




------------ REBUILD LOCAL INDEXES -- LAST


select 'alter index '||p.INDEX_NAME||' rebuild partition '|| PARTITION_NAME||' tablespace INDX1;'
from user_ind_partitions p, user_indexes i
where i.index_type='NORMAL' and p.index_name=i.index_name and i.table_name='PX_REFERENCE';