Sunday, August 12, 2018

How to change a non-partitioned table into a partitioned table in oracle along with indexes?

There are two ways to change the partitioned table into non-partitioned table.

1. We can use Oracle data pump (expdp/impdp) utilities with option PARTITION_OPTIONS=DEPARTITION.

CREATE OR REPLACE DIRECTORY TEST_DIR AS '/oracle/expimpdp/';
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO MY_USER;

EXPDP MY_USER/MY_PWD@MYDB TABLES=T DIRECTORY=TEST_DIR PARALLEL=5 INCLUDE=TABLE_DATA,INDEX COMPRESSION=ALL DUMPFILE=T_DUMP.DMP LOGFILE=EXPDP_T_DUMP.LOG

IMPDP MY_USER/MY_PWD@MYDB tables=T DIRECTORY=TEST_DIR PARALLEL=5 INCLUDE=TABLE_DATA,INDEX CONTENT=ALL PARTITION_OPTIONS=DEPARTITION DUMPFILE=T_DUMP.DMP LOGFILE=IMPDP_T_DUMP.LOG

2.  we can use ALTER TABLE - "ONLINE" & optional "UPDATE INDEXES"  Clause as below.

ALTER TABLE EMP_PART_CONVERT MODIFY PARTITION BY RANGE (employee_id) INTERVAL (100) 

PARTITION P1 VALUES LESS THAN (100), 
PARTITION P2 VALUES LESS THAN (500) ) 
ONLINE UPDATE INDEXES 
(
IDX1_SALARY LOCAL, 
IDX2_EMP_ID GLOBAL PARTITION BY RANGE (employee_id) ( PARTITION IP1 VALUES LESS THAN (MAXVALUE)
)
);

Please note following things  - When using the UPDATE INDEXES clause:

  • This clause can be used to change the partitioning state of indexes and storage properties of the indexes being converted.
  • Indexes are maintained both for the online and offline conversion to a partitioned table.
  • This clause cannot change the columns on which the original list of indexes are defined.
  • This clause cannot change the uniqueness property of the index.
  • This conversion operation cannot be performed if there are domain indexes.
  • During conversion - All Bitmap indexes become local partitioned indexes, by default.