Sunday, August 12, 2018

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

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

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

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=MERGE DUMPFILE=T_DUMP.DMP LOGFILE=IMPDP_T_DUMP.LOG

2. With simpler method as ALTER TABLE.
 
ALTER TABLE T1 MERGE PARTITIONS P1 TO P6 INTO P0;

3. Create a temporary copy of table along with data, drop the original table and rename the temp table name into the original and create index accordingly. (Considering that my table having not much huge data.)

CREATE TABLE T_TEMP AS
SELECT * FROM T;

RENAME T_TEMP TO T;

CREATE INDEX IDX_T AS T(ID);