In Oracle : Partitioning an ONLINE table in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Partitioning an ONLINE table in Oracle

 

 Partitioning an ONLINE table in Oracle

 

 

Here are the main topics for this article:

Present initial situation

 

The table EMP1 exist and must be partitioned on-line. Here is the definition of the EMP1 table :

 

                   CREATE TABLE EMP1

                            (  EMPNO NUMBER(10),

                               ENAME VARCHAR2(100 BYTE),

                               JOB VARCHAR2(9 BYTE),

                               MGR NUMBER(4),

                               HIREDATE DATE,

                               SAL NUMBER(7,2),

                               COMM NUMBER(7,2),

                               DEPTNO NUMBER(2)

                            )

                    TABLESPACE DATA_4_TBS;

 


                    CREATE UNIQUE INDEX PK_EMPNO ON EMP1(EMPNO);

 

                    ALTER TABLE EMP1 ADD (

                    CONSTRAINT PK_EMPNO PRIMARY KEY (EMPNO)

                    USING INDEX TABLESPACE USERS );

 

                     ALTER TABLE EMP1 ADD (

                     CONSTRAINT FK1 FOREIGN KEY (DEPTNO)

                     REFERENCES DEPT (DEPTNO));

 

           

Create a Partitioned Interim Table

 

                  CREATE TABLE emp_RANGE_part

                           (   EMPNO NUMBER(10),

                               ENAME VARCHAR2(100 BYTE),

                               JOB VARCHAR2(9 BYTE),

                               MGR NUMBER(4),

                               HIREDATE DATE,

                               SAL NUMBER(7,2),

                               COMM NUMBER(7,2),

                               DEPTNO NUMBER(2)

                            )

                 TABLESPACE DATA_4_TBS

                 PARTITION BY RANGE(HIREDATE)

                        (   PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-1995', 'DD-MON-YYYY')),

                            PARTITION p2 VALUES LESS THAN (MAXVALUE)

                        );

 

 

Start the Redefinition Process

 

1. Check if the Redefinition is possible, using "EXEC Dbms_Redefinition.Can_Redef_Table('SCOTT', 'EMP1');" command

    

Partitioning an ONLINE table in Oracle

 

If the execution return no errors, the redefinition is possible. 

 

2. Start the Redefinition Process

 

               BEGIN

                       DBMS_REDEFINITION.start_redef_table(

                                                                                         uname => 'SCOTT',

                                                                                         orig_table => 'EMP1',

                                                                                         int_table => 'EMP_RANGE_PART');

               END;

                 

        Partitioning an ONLINE table in Oracle

 

                After this command EMP_RANGE_PART is populated with EMP1 data and 2 tables (MLOG$_EMP1, RUPD$_EMP1) are created to keep the next 

            modifications made on the EMP1 table.  EMP_RANGE_PART become a kind of materialized view, but no job is created to refresh the 

           data in EMP_RANGE_PART table. EMP1 is accessible and can be modified using DML (INSERT, UPDATE, DELETE). 

 

           3. Synchronize the EMP1 and EMP_RANGE_PART table (optional: only if there are records in MLOG$_EMP1 and

               RUPD$_EMP1 tables )

             

            BEGIN

                    DBMS_REDEFINITION.sync_interim_table(

                               uname => 'SCOTT',

                               orig_table => 'EMP1',

                               int_table => 'EMP_RANGE_PART');

            END;

            

 

Create Constraints and Indexes

 

                 CREATE UNIQUE INDEX PK_EMPNO2 ON EMP_RANGE_PART(EMPNO)

                 TABLESPACE USERS;

 

                 ALTER TABLE EMP_RANGE_PART ADD (

                  CONSTRAINT PK_EMPNO2 PRIMARY KEY (EMPNO)

                 USING INDEX TABLESPACE USERS);

 

                 ALTER TABLE EMP_RANGE_PART ADD (

                 CONSTRAINT FK12 FOREIGN KEY (DEPTNO)

                 REFERENCES DEPT (DEPTNO));

 

 

Finish the Redefinition Process 

 

  BEGIN

       DBMS_REDEFINITION.finish_redef_table(

               uname => 'SCOTT',

               orig_table => 'EMP1',

               int_table => 'EMP_RANGE_PART');

  END;

 

DBMS_REDEFINITION.finish_redef_table procedure apply all the DML performed on EMP1 (information taken from MLOG$_EMP1,  RUPD$_EMP1) to EMP_RANGE_PART, and switch the name of the tables. 

Now the indexes and the constraints could be renamed using the commands (this is not mandatory):

 

ALTER INDEX <new index name> RENAME TO <initial index name>;    

ALTER TABLE EMP1 RENAME CONSTRAINT <new constraint name> TO <initial constraint name>;

          To verify that the partitions are created we can use the following command:

    

          SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'EMP1';  

 

        Partitioning an ONLINE table in Oracle

 

     

         Gather statistics for the partitioned table

 

         For a better performance the statistics must be gathered at the end of the partitioning:

 

         exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'SCOTT', tabname=>'EMP1', estimate_percent=>10); 

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Partitioning an ONLINE table in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.