Database partitioning

[Mohammed Hamed]

Hello Erman,

I have few table is oracle EBS custom module, that table are quiet huge ranging from more that 30+ Gb to nearly 500GB tables each , these table contains the data from year 2000 , we need it to partition it yearly basis, I am trying it using datapump.
1. Exporting the non partitioned table.
2. Creating new table with partition and doing the import.

Issue here is that impdp is taking very long time as the table size is huge.
can you please suggest quickest way of doing it.

Right now i don't want to touch the original table, just want to play with the backup table.
DB version 11.2.0.3 running on HP-UX

[ErmanArslansOracleBlog]

Normally, we have 5 general methods to convert a nonpartitioned table to a partitioned one;

A) Export/import method
B) Insert with a subquery method
C) Partition exchange method
D) DBMS_REDEFINITION
E) MODIFY clause added to the ALTER TABLE SQL Statement. (From 12.2)

Check -> How to Partition a Non-partitioned / Regular / Normal Table (Doc ID 1070693.6)

That slowness should be analyzed in the right context, but did you check the following MOS note? There are lots of issue reported there ->

DataPump Import (IMPDP) Performance Known Problems (Doc ID 1948188.1)

Also, as an alternative; did you check the ONLINE REDEFINITION?

AN EXAMPLE OF A COMPLEX ONLINE TABLE REDEFINITION (DBMS_REDEFINITION) (Doc ID 1358236.1)

[ErmanArslansOracleBlog]

CTAS or INSERT APPEND PARALLEL - based method may be more feasible..

Example For CTAS;

SQL>alter session enable parallel dml;
SQL> create table partbl (qty, name) partition by range (qty) (partition p1 values less than (501),partition p2 values less than (maxvalue))
  2  as select /*+PARALLEL*/ * from origtbl;


Example for INSERT :

SQL>alter session enable parallel dml;
SQL> insert /*+APPEND PARALLEL*/ into partbl (qty, name) select * from origtbl;