For convert a Partition table into a Non-Partition table in Oracle

We’ll explore two methods to achieve this conversion:

1. Using Create Table As Select (CTAS)
2. Using Oracle Data Pump

Method 1: Using Create Table As Select (CTAS)

The CTAS approach involves creating a new non-partitioned table by selecting the data from the existing partitioned table. Once data is copied, the partitioned table is dropped and replaced by the newly created table.

Steps to Convert Using CTAS:

Generate the DDL for the Non-Partition table which include indexes and contraints

1. Create the Non-Partitioned Table

CREATE TABLE new_non_partitioned_table AS
SELECT * FROM partitioned_table;

2. Recreate Indexes and Constraints.

3. Verify the data is equal.

4. Drop the Original Partitioned Table.

DROP TABLE partitioned_table;

5. Rename the New Table

ALTER TABLE new_non_partitioned_table RENAME TO partitioned_table;

Method 2: Using Oracle Data Pump

Oracle Data Pump provides a more structured way to export and import table data. It allows for flexible transformations during the data movement process. In this case, we will export the partitioned table and import it into a new, non-partitioned table.

Steps to Convert Using Oracle Data Pump:

1. Export the Partitioned Table

expdp username/password@dbname DIRECTORY=dpump_dir1 DUMPFILE=partitioned_table.dmp 
LOGFILE=partitioned_table_exp.log TABLES=partitioned_table

2. Create the Non-Partitioned Table

CREATE TABLE new_non_partitioned_table (
  column1 datatype,
  column2 datatype,
  ...
);

3. Import the Data Into the Non-Partitioned Table

impdp username/password@dbname DIRECTORY=dpump_dir1 DUMPFILE=partitioned_table.dmp 
LOGFILE=partitioned_table_imp.log REMAP_TABLE=partitioned_table:new_non_partitioned_table

4. Recreate Indexes and Constraints.

CREATE INDEX idx_column_name ON new_non_partitioned_table(column_name);

ALTER TABLE new_non_partitioned_table
ADD CONSTRAINT pk_column_name PRIMARY KEY (column_name);

5. Drop the Original Partitioned Table

DROP TABLE partitioned_table;

6. Rename the New Table

ALTER TABLE new_non_partitioned_table RENAME TO partitioned_table;