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-00923: FROM keyword not found where expected

Problem Symptom While performing a simple select statement it fails with error ORA-00923: FROM keyword not found where expected like below. SQL> select 48*1024*1024*1024 decimal from dual; select 48*1024*1024*1024 decimal from dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected Cause of the problem The ORA-00923 can be caused by two different reasons. 1)Oracle itself expect FROM keyword in the position but it could not find any. Like below, SQL> select 48*1024 om dual; select 48*1024 om dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected As no from keyword found so error arises. Oracle expected from in place of keyword dual. 2)The secong cause can sometimes mislead you. Like in case of SQL> select 48*1024*1024*1024 decimal from dual; select 48*1024*1024*1024 decimal from dual * ERROR at line 1: ORA-00923: FROM keyword not found where exp
Read more

How to make partitioning in Oracle more Quickly

As the table size grows and full table scans happens more frequently then there is no alternative than partitioning in oracle. Partition greatly enhance performance of a query. I will start my partitioning system simply with an example. In my database the following query took 30~35 seconds to complete. After partitioning the performance increased amazingly and it then took only 1 second. My query was, SELECT DISTINCT fs.type, fs.id , fs.pid, fs.cid , fs.cr_id, fs.created_date FROM summary fs where fs.id in (select fa.id from forms fa where fa.sar in (select la.sar from login la where la.login_id=1 and fa.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') and fs.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') ) )and mode=0; After seeing above query I decide to make range partition on column created_date both in summary table and forms table. Below is the list of procedur
Read more

Copy files between Unix and Windows with rcp

There are many third party tools by which you can copy files between windows and unix machine. Some common tools are winscp, SSHSecureShellClient etc. Instead of using any third party tool, you can use original unix and windows built-in tool to do the task. The RCP (Remote CoPy) is a standard UNIX tool and a Microsoft Windows 2000 tool that copies files/folder to and from computer running the RCP service. Before you can use the RCP tool from a Windows-based computer you must do something in your UNIX computer. you must turn on the RCP service and update the security files to allow the Windows-based computer to connect to your unix machine over this service. The following steps will help you. Step 01: Turn on RCP service on UNIX Host Turn on the RCP service on unix machine by using inetd: 1. Log on as root. 2. Edit the file /etc/Inetd.conf. 3. Uncomment the lines that start with shell and that start with exec. 4. Save the file. 5. Use ps -ef |grep inetd and determine inetd service p
Read more