We will do partition of table OUT_CDR which reside on CR_2 schema. We will do partition on column CDATE using RANGE partitioning technique. We will not change any table structure other than partition. All indexes, constraints, triggers, privileges defined on the table will be remain same.
Step 01: Let's have a look at the table on which we will do partitioning.
SQL> set pagesize 200
SQL> 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 STATISTICS
STORAGE(INITIAL 168820736 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 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 STATISTICS
STORAGE(INITIAL 522190848 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 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 STATISTICS
STORAGE(INITIAL 521142272 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 2013265920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 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=GREGORIA
P01152009 TO_DATE(' 2009-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P02012009 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P08152008 TO_DATE(' 2008-08-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P09012008 TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P09152008 TO_DATE(' 2008-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P10012008 TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P10152008 TO_DATE(' 2008-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P11012008 TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P11152008 TO_DATE(' 2008-11-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P12012008 TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P12152008 TO_DATE(' 2008-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PMAX MAXVALUE
13 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 VALID
OUT_CDR_UQ VALID
OUT_CDR_UQ_2 VALID
Step 08: Drop the interim table OUT_CDR_.
SQL> DROP TABLE OUT_CDR_;Table dropped.
Related DocumentsORA-12091: cannot online redefine table with materialized viewsHow to Convert Long data type to Lob
