How to convert non-partitioned table to partition table using re-definition

SQL> set pagesize 200SQL> set long 999999
SQL> set linesize 150
SQL> select dbms_metadata.get_ddl('TABLE','OUT_CDR','CR_2') from dual;

DBMS_METADATA.GET_DDL('TABLE','OUT_CDR','CR_2')
--------------------------------------------------------------------------------

CREATE TABLE "CR_2"."OUT_CDR" ( "ID" NUMBER(32,0) NOT NULL ENABLE, "CDATE" DATE NOT NULL ENABLE,
"DDATE" DATE NOT NULL ENABLE,
"ACCTSESSIONID" VARCHAR2(100),
"CALLINGNO" VARCHAR2(100),
"CALLEDNO" VARCHAR2(100) NOT NULL ENABLE,
"AREACODE" VARCHAR2(100),
"PREFIX" VARCHAR2(100),
"SESSIONTIME" NUMBER(32,0),
"BILLABLETIME" NUMBER(32,0),
"RATE" NUMBER(32,4),
"CALL_COST" NUMBER(32,4),
"CURRENTBILL" NUMBER(32,4),
"DISCONNECTCAUSE" VARCHAR2(50),
"SOURCEIP" VARCHAR2(100),
"DESTIP" VARCHAR2(100),
"BILLABLE" NUMBER(32,0) NOT NULL ENABLE,
"LESS" NUMBER(32,0) NOT NULL ENABLE,
"ACCID" NUMBER(32,0),
"IN_DDATE" DATE,
"IN_PREFIX" VARCHAR2(100),
"IN_SESSIONTIME" NUMBER(32,0),
"IN_BILLABLETIME" NUMBER(32,0),
"IN_RATE" NUMBER(32,4),
"IN_CALL_COST" NUMBER(32,4),
"IN_MONEYLEFT" NUMBER(32,4),
"IN_DISCONNECTCAUSE" VARCHAR2(50),
"IN_BILLABLE" NUMBER(32,0),
"IN_LESS" NUMBER(32,0), "SWITCH_ID
" NUMBER(32,0) NOT NULL ENABLE,
"USER_ID" NUMBER(32,0) NOT NULL ENABLE,
"IN_USER_ID" NUMBER(32,0),
"PROCESSED" NUMBER(1,0),
CONSTRAINT "OUT_CDR_PK" PRIMARY KEY ("ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 168820736 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "CDR_INDX_SPC" ENABLE, CONSTRAINT "OUT_CDR_UQ" UNIQUE ("CDATE", "CALLEDNO", "USER_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 522190848 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "CDR_INDX_SPC" ENABLE, CONSTRAINT "OUT_CDR_UQ_2" UNIQUE ("DDATE", "CALLEDNO", "USER_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 521142272 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "CDR_INDX_SPC" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 2013265920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "OUT_CDR_NEW_SPC"


Step 02: Let's determine if the table OUT_CDR can be redefined online.
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.

Step 03: Create a interim table which holds the same structure as the original table except constraints, indexes, triggers but add the partitioning attribute.I named the interim table as OUT_CDR_. Later we may drop it.

SQL> CREATE TABLE "CR_
2"."OUT_CDR_"2 ( "ID" NUMBER(32,0),
3 "CDATE" DATE ,
4 "DDATE" DATE ,
5 "ACCTSESSIONID" VARCHAR2(100),
6 "CALLINGNO" VARCHAR2(100),
7 "CALLEDNO" VARCHAR2(100) ,
8 "AREACODE" VARCHAR2(100),
9 "PREFIX" VARCHAR2(100),
10 "SESSIONTIME" NUMBER(32,0),
11 "BILLABLETIME" NUMBER(32,0),
12 "RATE" NUMBER(32,4),
13 "CALL_COST" NUMBER(32,4),
14 "CURRENTBILL" NUMBER(32,4),
15 "DISCONNECTCAUSE" VARCHAR2(50),
16 "SOURCEIP" VARCHAR2(100),
17 "DESTIP" VARCHAR2(100),
18 "BILLABLE" NUMBER(32,0) ,
19 "LESS" NUMBER(32,0) ,
20 "ACCID" NUMBER(32,0),
21 "IN_DDATE" DATE,
22 "IN_PREFIX" VARCHAR2(100),
23 "IN_SESSIONTIME" NUMBER(32,0),
24 "IN_BILLABLETIME" NUMBER(32,0),
25 "IN_RATE" NUMBER(32,4),
26 "IN_CALL_COST" NUMBER(32,4),
27 "IN_MONEYLEFT" NUMBER(32,4),
28 "IN_DISCONNECTCAUSE" VARCHAR2(50),
29 "IN_BILLABLE" NUMBER(32,0),
30 "IN_LESS" NUMBER(32,0),
31 "SWITCH_ID" NUMBER(32,0) ,
32 "USER_ID" NUMBER(32,0) ,
33 "IN_USER_ID" NUMBER(32,0),
34 "PROCESSED" NUMBER(1,0)
35 ) TABLESPACE "OUT_CDR_NEW_SPC"36 Partition by range(cdate)37 (38 partition P08152008 values less than (to_date('15-AUG-2008','DD-MON-YYYY')),39 partition P09012008 values less than (to_date('01-SEP-2008','DD-MON-YYYY')),40 partition P09152008 values less than (to_date('15-SEP-2008','DD-MON-YYYY')),41 partition P10012008 values less than (to_date('01-OCT-2008','DD-MON-YYYY')),42 partition P10152008 values less than (to_date('15-OCT-2008','DD-MON-YYYY')),43 partition P11012008 values less than (to_date('01-NOV-2008','DD-MON-YYYY')),44 partition P11152008 values less than (to_date('15-NOV-2008','DD-MON-YYYY')),45 partition P12012008 values less than (to_date('01-DEC-2008','DD-MON-YYYY')),46 partition P12152008 values less than (to_date('15-DEC-2008','DD-MON-YYYY')),47 partition P01012009 values less than (to_date('01-JAN-2009','DD-MON-YYYY')),48 partition P01152009 values less than (to_date('15-JAN-2009','DD-MON-YYYY')),49 partition P02012009 values less than (to_date('01-FEB-2009','DD-MON-YYYY')),50 partition PMAX values less than (maxvalue));
Table created.

Step 04: Initiates the redefinition process by calling dbms_redefinition.start_redef_table procedure.
SQL> exec dbms_redefinition.start_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');
PL/SQL procedure successfully completed.


Step 05: Copies the dependent objects of the original table onto the interim table. The COPY_TABLE_DEPENDENTS Procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. But this procedure does not clone the already registered dependent objects.In fact COPY_TABLE_DEPENDENTS Procedure is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table which in facr represents the post-redefinition table.

SQL> declare
2 error_count pls_integer := 0;
3 BEGIN
4 dbms_redefinition.copy_table_dependents('CR_2', 'OUT_CDR', 'OUT_CDR_',1, true, true, true, false,error_count);
5 dbms_output.put_line('errors := ' to_char(error_count));
6 END;
7 /PL/SQL procedure successfully completed.



Step 06: Completes the redefinition process by calling FINISH_REDEF_TABLE Procedure.
SQL> exec dbms_redefinition.finish_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');
PL/SQL procedure successfully completed.


Step 07: Check the partitioning validation by,
SQL> Select partition_name, high_value from user_tab_partitions where table_name='OUT_CDR';PARTITION_NAME HIGH_VALUE------------------------------ ---------------------------------------------------------------------------------------------------P01012009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP01152009 TO_DATE(' 2009-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP02012009 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP08152008 TO_DATE(' 2008-08-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP09012008 TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP09152008 TO_DATE(' 2008-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP10012008 TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP10152008 TO_DATE(' 2008-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP11012008 TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP11152008 TO_DATE(' 2008-11-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP12012008 TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP12152008 TO_DATE(' 2008-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAPMAX MAXVALUE13 rows selected.Check index status by,SQL> select index_name , status from user_indexes where table_name='OUT_CDR';INDEX_NAME STATUS------------------------------ --------OUT_CDR_PK VALIDOUT_CDR_UQ VALIDOUT_CDR_UQ_2 VALIDStep 08: Drop the interim table OUT_CDR_.SQL> DROP TABLE OUT_CDR_;Table dropped.