Partitioning an existing non partitioned table with data, index and constraints - PART I

Method I: Using dbms_redefinition.

Step 1: Create my table:

SQL>
SQL> drop table not_paritioned_tbl purge;

Table dropped.

SQL> CREATE TABLE not_partitioned_tbl AS
  2  SELECT object_id,object_name,created,owner FROM all_objects;

Table created.

SQL> ALTER TABLE not_partitioned_tbl ADD CONSTRAINT not_partitioned_tbl_pk PRIMARY KEY (object_id);

Table altered.

SQL> CREATE INDEX npt_created ON not_partitioned_tbl(created);

Index created.

SQL> EXECUTE DBMS_STATS.gather_table_stats('SCOTT','NOT_PARTITIONED_TBL',cascade=>true);

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM not_partitioned_tbl;

  COUNT(*)
----------
     42540

SQL>

Step 2: Create a partitioned table.
SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE partitioned_tbl (object_id NUMBER, object_name VARCHAR2(30), created DATE, owner VARCHAR2(30))
  2  PARTITION BY RANGE (created)
  3  (PARTITION pt_2005 VALUES LESS THAN (to_date('01/01/2006','dd/mm/rrrr')),
  4   PARTITION pt_2006 VALUES LESS THAN (to_date('01/01/2007','dd/mm/rrrr')),
  5   PARTITION pt_2007 VALUES LESS THAN (to_date('01/01/2008','dd/mm/rrrr')),
  6*  PARTITION pt_rest VALUES LESS THAN (MAXVALUE))
SQL> /

Table created.

Step 3: Verify whether we can redefine the existing table.

SQL> EXEC DBMS_REDEFINITION.can_redef_table('SCOTT','NOT_PARTITIONED_TBL');

PL/SQL procedure successfully completed.

SQL>

Step 4: Status of original and interim table

SQL> SELECT table_name,partitioned FROM all_tables
  2  WHERE table_name IN ('NOT_PARTITIONED_TBL','PARTITIONED_TBL');

TABLE_NAME               PAR
------------------------------ ---
PARTITIONED_TBL            YES
NOT_PARTITIONED_TBL           NO

SQL>

Step 4: Connect as sys (I don't want to give grants to scott) and start redefinition.

oracle@ubuntu-desktop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 2 15:37:17 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> BEGIN
  2   DBMS_REDEFINITION.start_redef_table('SCOTT','NOT_PARTITIONED_TBL','PARTITIONED_TBL');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','NOT_PARTITIONED_TBL','PARTITIONED_TBL');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>

Step 5: Create index, constraints.

SQL> ALTER TABLE partitioned_tbl ADD CONSTRAINT partitioned_tbl_pk PRIMARY KEY (object_id);

Table altered.

SQL> CREATE INDEX pt_created ON partitioned_tbl(created);

Index created.

SQL> EXECUTE DBMS_STATS.gather_table_stats('SCOTT','PARTITIONED_TBL',cascade=>true);

PL/SQL procedure successfully completed.

SQL>

Step 6: Finish the redef (as sys)

SQL> EXECUTE dbms_redefinition.finish_redef_table('SCOTT','NOT_PARTITIONED_TBL','PARTITIONED_TBL');

PL/SQL procedure successfully completed.

SQL>

Step 7: Now non_partitioned_tbl should become partitioned.

SQL> SELECT table_name,partitioned FROM all_tables
  2  WHERE table_name IN ('NOT_PARTITIONED_TBL','PARTITIONED_TBL');

TABLE_NAME               PAR
------------------------------ ---
NOT_PARTITIONED_TBL           YES
PARTITIONED_TBL            NO

SQL>

SQL> DROP TABLE partitioned_tbl purge;

Table dropped.

SQL>
Step 8: You can rename the index and constraints to match the table name.

SQL> SELECT index_name,table_name FROM user_indexes WHERE table_name='NOT_PARTITIONED_TBL';

INDEX_NAME               TABLE_NAME
------------------------------ ------------------------------
PARTITIONED_TBL_PK           NOT_PARTITIONED_TBL
PT_CREATED               NOT_PARTITIONED_TBL

SQL> SELECT constraint_name,table_name FROM user_constraints WHERE table_name='NOT_PARTITIONED_TBL';

CONSTRAINT_NAME            TABLE_NAME
------------------------------ ------------------------------
PARTITIONED_TBL_PK           NOT_PARTITIONED_TBL

SQL>

Further Reading:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484
http://www.oracle-base.com/articles/misc/PartitioningAnExistingTable.php
http://www.oracle-base.com/articles/misc/PartitioningAnExistingTableUsingExchangePartition.php