Hi, We are using version 11.2.0.4 of oracle Exadata. We have one existing table(PART_EXISTING) which is range partitioned on column- CREATE_DATE, we want to change the partitioning strategy as per new business requirement and now it will be same range partitioned but on a different date column PART_DATE. The current partition table is holding ~1billion rows with ~5 partitions worth of data (each having ~200million rows) and its having three different indexes on it on it which are all local index. The new partitioned table will have one composite unique key index only with the new partition key. We want to make this happen in quick possible time. I am planning to go for partition exchange method as below, Wanted to know from experts if below method is okay or if any issue with below and there is any better/faster way to achieve this? We will ensure that during this activity the base table(PART_EXISTING) wont have any DML operation happen on it.
So we have three tables
"PART_EXISTING" range partitioned by CREATE_DATE holding ~5 partitions and having ~200million in each partition. With three indexes on it.
"PART_NEW" table with exact same column as that of table "PART_EXISTING" and one composite unique index on it with range partition key as PART_DATE.
"INTERIM" table which is non partitioned having no index on it.
below is how the script will look like:-
We will make the unique index unusable for the table PART_NEW.
ALTER TABLE PART_EXISTING EXCHANGE PARTITION PART_EXISTING_DATE1 WITH TABLE interim INCLUDING INDEXES WITHOUT VALIDATION;;
ALTER TABLE PART_NEW EXCHANGE PARTITION PART_NEW_DATE1 WITH TABLE interim INCLUDING INDEXES WITHOUT VALIDATION;
truncate table interim;
ALTER TABLE PART_EXISTING EXCHANGE PARTITION PART_EXISTING_DATe2 WITH TABLE interim INCLUDING INDEXES WITHOUT VALIDATION;
ALTER TABLE PART_NEW EXCHANGE PARTITION PART_NEW_DATE2 WITH TABLE interim INCLUDING INDEXES WITHOUT VALIDATION;
truncate table interim;
ALTER TABLE PART_EXISTING EXCHANGE PARTITION PART_EXISTING_DATE3 WITH TABLE interim INCLUDING INDEXES WITHOUT VALIDATION;
ALTER TABLE PART_NEW EXCHANGE PARTITION PART_NEW_DATE3 WITH TABLE interim INCLUDING INDEXES WITHOUT VALIDATION;
.
.
.
Alter table PART_NEW rename to PART_EXISTING;
The base table "PART_EXISTING" would have been truncated by its own during partition exchange so we just need to drop it.