Creating partitioned table using “Partition Exchange” method

Need to partition a heap table which had more than million records stored. The table had to be partitioned on the basis of date range. The data in the table had to be partitioned half yearly. Firstly, considering the export/import option, there would be need of extra space on the server to hold the export dump of the table, which was not available at that point of time. After some research and going through Oracle documentation, it was finally decided to give a try on Oracle Partition Exchange method.

In partition exchange method, The non-partitioned table can be partitioned by exchanging the data segments. Exchange paritioning method gives the flexibility to convert hash partitioned to range partitioned table. Likewise, convert list partitioned table to range paritioned table or vice versa.

Check the table which is good candidate for partitioning.

SQL> select count(*) from test;    

 COUNT(*)
----------
   162776

The condition being considered here is that the table has to be partitioned based on the date range, which would be on “CREATED” column from the table. Create another table with partitions:

SQL> CREATE TABLE TEST_PRT (
 2  OWNER                  VARCHAR2(30)     NOT NULL,
 3  OBJECT_NAME            VARCHAR2(30)     NOT NULL,
 4  SUBOBJECT_NAME         VARCHAR2(30)             ,

 5  OBJECT_ID              NUMBER              NOT NULL,
 6  DATA_OBJECT_ID         NUMBER                      ,
 7  OBJECT_TYPE            VARCHAR2(19)             ,
 8  CREATED                DATE                NOT NULL,

 9  LAST_DDL_TIME          DATE                NOT NULL,
10  TIMESTAMP              VARCHAR2(19)                ,
11  STATUS                 VARCHAR2(7)                 ,
12  TEMPORARY              VARCHAR2(1)                 ,

13  GENERATED              VARCHAR2(1)                 ,
14  SECONDARY              VARCHAR2(1)                 )
15  PARTITION BY RANGE (LAST_DDL_TIME)
16  (
22      PARTITION MAX_VALUE VALUES LESS THAN (MAXVALUE)

23  )
SQL> /    

Table created.

Check the partitions created for TEST_PRT table.

SQL> select partition_name from user_tab_partitions where table_name = 'TEST_PRT';    

PARTITION_NAME
------------------------------
MAX_VALUE    

1 rows selected.

Exchange the data in “TEST” table with partitions in “TEST_PRT” table.

SQL> alter table test_prt exchange partition max_value with table test without validation;    

Table altered.

Now split the partitions to break up the data into respective partitions.

ALTER TABLE TEST_PRT
 SPLIT PARTITION max_value AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
 INTO (PARTITION p1,
       PARTITION max_value);    


ALTER TABLE TEST_PRT
 SPLIT PARTITION max_value AT (TO_DATE('30-JUN-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
 INTO (PARTITION p2,
       PARTITION max_value);    

ALTER TABLE TEST_PRT

 SPLIT PARTITION max_value AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
 INTO (PARTITION p3,
       PARTITION max_value);    

ALTER TABLE TEST_PRT
 SPLIT PARTITION max_value AT (TO_DATE('30-JUN-2007 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))

 INTO (PARTITION p4,
       PARTITION max_value);    

ALTER TABLE TEST_PRT
 SPLIT PARTITION max_value AT (TO_DATE('31-DEC-2007 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
 INTO (PARTITION p5,

       PARTITION max_value);

All the rows in the “TEST” table has been moved to respective partitions in “TEST_PRT” table. Check “TEST” table to confirm the same.

SQL> select count(*) from test;    

 COUNT(*)
----------
        0

Check the data in the partitions.

SQL> exec dbms_stats.gather_table_stats('ADMIN','TEST_PRT');    

PL/SQL procedure successfully completed.    

SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'TEST_PRT';    


PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
MAX_VALUE                          163008
P1                                      0
P2                                      0

P3                                    100
P4                                     80
P5                                      0

Thus, the data is moved from heap table to partitioned table without any need of extra free space in the database. After successful migration of data into respective partitions, drop the original table “TEST” and rename table “TEST_PRT” as “TEST”.