Redefining Tables Online using DBMS_REDEFINITION

Redefining Tables Online

DBMS_REDEFINITION

Sometime it's necessary in oracle database to modify the logical or physical structure of a table to improve the poor performance or managed space issue:

-  Improve the performance of queries or DML

-  Accommodate application changes

-  Manage storage

Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.

When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process. The table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users.

Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined.

There are n number of ways to reorganization of table. When downtime is a challenge to us then the best option we can use DBMS_REDEFINITION package to execute the task.

Redefine a table online:

1.  Choose the redefinition method: by key or by rowids:

By key  Select a primary key or pseudo-primary key to use for the redefinition. Pseudo-primary keys are unique keys with all component columns having NOT NULL constraints. For this method, the versions of the tables before and after redefinition should have the same primary key columns. This is the preferred and default method of redefinition.

By rowid Use this method if no key is available. In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table. It is recommended that this column be dropped or marked as unused after the redefinition is complete. If COMPATIBLE is set to 10.2.0 or higher, the final phase of redefinition automatically sets this column unused. You can then use the ALTER TABLE ... DROP UNUSED COLUMNS statement to drop it.

Note : You cannot use this method on index-organized tables.

2.  Verify that the table can be redefined online by invoking the CAN_REDEF_TABLE procedure. If the table is not a candidate for online redefinition, then this procedure raises an error indicating why the table cannot be redefined online.

3.  Create an empty interim table (in the same schema as the table to be redefined) with all of the desired logical and physical attributes.

4.  It is not necessary to create the interim table with all the indexes, constraints, grants, and triggers of the table being redefined.  This can be done by COPY_TABLE_DEPENDENTS procedure to automatically.

5.  For large table and want to improve the performance we can set it in parallel :

alter session force parallel dml parallel degree-of-parallelism;

alter session force parallel query parallel degree-of-parallelism;

6.  FINISH_REDEF_TABLE procedure to complete the redefinition of the table. During this procedure, the original table is locked in exclusive mode for a very short time, independent of the amount of data in the original table. However, FINISH_REDEF_TABLE will wait for all pending DML to commit before completing the redefinition.

7.  If you used rowids for the redefinition and your COMPATIBLE initialization parameter is set to 10.1.0 or lower, drop or set UNUSED the hidden column M_ROW$$ that is now in the redefined table.

ALTER TABLE <table_name> SET UNUSED (M_ROW$$);

     If COMPATIBLE is 10.2.0 or higher, this hidden column is automatically set UNUSED when redefinition completes. You can then drop the column with the ALTER TABLE ... DROP UNUSED COLUMNS statement.

Wait for any long-running queries against the interim table to complete and then drop the interim table.

Here is the brief demonstration for redefining table.

[oracle@vm215 ~]$ sqlplus amit/amit

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 29 05:44:44 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Creating a demo table name test1 under AMIT schema.

SQL> CREATE TABLE TEST1 ( ID        NUMBER(10) ,

                          ENAME     VARCHAR2(10),

                          SAL       NUMBER(10) ) ;

Inserting bulk rows.

Set the pga_aggregate_target to max value.

SQL> INSERT INTO AMIT.TEST1 SELECT ROWNUM, 'T'|| ROWNUM,DBMS_RANDOM.VALUE(100000, 999999) FROM DUAL CONNECT BY LEVEL < 1000000;

999999 ROWS CREATED.

SQL> COMMIT ;

COMMIT COMPLETE.

Creating dependent objects related to table TEST1 rows, so that you can see what happen when we do online redefinition.

View Creation

SQL> CREATE VIEW TEST1_VW AS SELECT * FROM TEST1 ;

VIEW CREATED.

Sequence Creation

SQL> CREATE SEQUENCE TEST_SEQ ;

SEQUENCE CREATED.

Procedure Creation

CREATE OR REPLACE PROCEDURE PROC1 (P_ID IN NUMBER)

AS

  V_ID  NUMBER ;

BEGIN

  SELECT SAL

  INTO   V_ID

  FROM   TEST1

  WHERE  ID = P_ID;

END;

/

PROCEDURE CREATED.

DML Trigger Creation

SQL> CREATE OR REPLACE TRIGGER AMIT_TRIG

      BEFORE INSERT OR UPDATE ON TEST1

       FOR EACH ROW

DECLARE

      X       NUMBER;

BEGIN

     SELECT COUNT(*) INTO X

     FROM TEST1

     WHERE ID = :NEW.ID;

IF X > 0 THEN

           RAISE_APPLICATION_ERROR(-20501, 'ID' || :NEW.ID || ' ALREADY EXISTS');

   END IF;

END;

/

TRIGGER CREATED.

Primary Key Creation

SQL> ALTER TABLE TEST1 ADD CONSTRAINT TEST1_ID_PK PRIMARY KEY (ID) ;

TABLE ALTERED.

Get the status before redefinition

SQL> SQL>

SQL> COLUMN OBJECT_NAME FORMAT A20

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS ORDER BY OBJECT_NAME;SQL>

OBJECT_NAME          OBJECT_TYPE         STATUS

-------------------- ------------------- -------

AMIT_TRIG            TRIGGER             VALID

PROC1                PROCEDURE           VALID

TEST1                TABLE               VALID

TEST1_ID_PK          INDEX               VALID

TEST1_VW             VIEW                VALID

TEST_SEQ             SEQUENCE            VALID

6 ROWS SELECTED.

Check table can be redefined online using either rowids or primary key

Using Primary Key

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

Using rowid

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

Create a replica of new interim table without any dependent objects

SQL> CREATE TABLE TEST1_REORG AS SELECT * FROM TEST1 WHERE ROWNUM=5 ;

TABLE CREATED.

SQL> SELECT COUNT(*) FROM TEST1_REORG ;

  COUNT(*)

----------

         0

SQL>  SELECT COUNT(*) FROM TEST1;

  COUNT(*)

----------

    999999

Now connect using privilege user to execute the task

[oracle@vm215 ~]$ sqlplus / as sysdba

Sql*plus: release 11.2.0.3.0 production on sat oct 29 05:16:48 2016

Copyright (c) 1982, 2011, oracle.  All rights reserved.

Connected to:

Oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production

With the partitioning, olap, data mining and real application testing options

Start redefinition using Primary Key

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('AMIT','TEST1', 'TEST1_REORG');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

Copy dependent objects (it will automatically copy dependent objects) like mview, primary key, view, sequence, triggers. 

Note : IGNORE_ERROR  is set to true here to avoid primary key violation (COPY_TABLE_DEPENDENTS)

SQL> DECLARE

      N PLS_INTEGER;

BEGIN

      DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('AMIT', 'TEST1','TEST1_REORG',         DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, N);

END;

/ 

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

To check for errors in dba_redefinition_errors view

SQL> COL OBJECT_NAME FOR A25

SET LIN200 PAGES 200

COL DDL_TEXT FOR A60

SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT

FROM DBA_REDEFINITION_ERRORS;

NO ROWS SELECTED

Validate the row count on both tables and sync with interim table

SQL> SELECT COUNT(*) FROM AMIT.TEST1_REORG ;

  COUNT(*)

----------

    999999

SQL> SELECT COUNT(*) FROM AMIT.TEST1 ;

  COUNT(*)

----------

    999999

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('AMIT', 'TEST1', 'TEST1_REORG');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

Finally finish the redefinition

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('AMIT', 'TEST1', 'TEST1_REORG');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> COLUMN OBJECT_NAME FORMAT A40

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS

FROM DBA_OBJECTS

WHERE OWNER='AMIT';

OBJECT_NAME           OBJECT_TYPE         STATUS

--------------------- ------------------- -------

TEST1_VW              VIEW                INVALID

TEST_SEQ              SEQUENCE            VALID

PROC1                 PROCEDURE           VALID

TEST1                 TABLE               VALID

TEST1_REORG           TABLE               VALID

TEST1_ID_PK           INDEX               VALID

TMP$$_TEST1_ID_PK0    INDEX               VALID

TMP$$_AMIT_TRIG0      TRIGGER             INVALID

AMIT_TRIG             TRIGGER             INVALID

9 ROWS SELECTED.

You can see the status of view and trigger is invalid.  Recompile the schema with complete dependency.

SQL> EXEC UTL_RECOMP.RECOMP_SERIAL('AMIT') ;

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='AMIT';

OBJECT_NAME                              OBJECT_TYPE         STATUS

---------------------------------------- ------------------- -------

TEST1_VW                                 VIEW                VALID

TEST_SEQ                                 SEQUENCE            VALID

PROC1                                    PROCEDURE           VALID

TEST1                                    TABLE               VALID

TEST1_REORG                              TABLE               VALID

TEST1_ID_PK                              INDEX               VALID

TMP$$_TEST1_ID_PK0                       INDEX               VALID

TMP$$_AMIT_TRIG0                         TRIGGER             VALID

AMIT_TRIG                                TRIGGER             VALID

9 ROWS SELECTED.

At the end drop the interim table.

SQL> DROP TABLE AMIT.TEST1_REORG;

TABLE DROPPED.

Hope this help  :)