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

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.

Comments

Post a Comment

Popular posts from this blog

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq
Read more

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned. ORA-00939: too many arguments for function But surprisingly on Oracle 9.2 database the same code does not return any error. Cause of the Problem As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255. Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767. Solution of the Problem Opt
Read more

ORA-31655: no data or metadata objects selected for job

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining
Read more