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.

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);