DBA Tips Archive for Oracle

  


Online Data Reorganization and Data Redefinition - (Oracle 9i)

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. New Online Capabilities
  3. Restrictions for Redefining Tables Online
  4. The DBMS_REDEFINITION PL/SQL Package
  5. Create User and Initial Objects
  6. Initial EMP Table
  7. Determine if Table is a Candidate for Online Reorganization
  8. Create Empty Interim Table Reflecting the Final Structure
  9. Start the Reorganization Process
  10. Create Indexes, Constraints, Triggers, and Grants on Interim Table
  11. Simulate Some DML Activity on Source Table
  12. Complete the Redefinition Process
  13. The Cleanup Process
  14. Aborting the Process



Overview

Oracle8i introduced many new features that allowed for online data reorganization such as creating indexes online, rebuilding indexes online, coalescing indexes online and moving index-organized tables (IOTs) online. Oracle9i continues its commit to overall database availability and reducing downtime with a new feature that allows online data reorganizations and data redefinition. This new feature allows the DBA to perform online data reorganization and redefinition while users are allowed full access to the database during the reorganization. It is now possible to modify physical attributes of a table and transform both data and table structure while allowing users full access to the database object being redefined.

This article provides a detailed look into the new Oracle9i online data redefinition feature and on example on how to use this new feature.



New Online Capabilities

DBAs responsible for large and active databases are always looking for solutions to reduce downtime while performing database structure changes. With the new online data reorganization and data redefinition features, DBAs can now perform a significant portion of the changes required while users have full access to the table being redefined. During the redefinition process, it is accessible for read and write operations by all users. The DBA also have control over when to switch from the original to the newly redefined table. The actual switch process is very brief and is completely independent of the size of the table or the complexity of the redefinition.

This feature can really come in hand during an application upgrade that requires changes to underlying tables. During the application update, the DBA can start the definition process, and then periodically synchronize the interim table so that the interim table includes the latest changes to the original table. This allows to reduce the amount of time to complete the final step in the redefinition process. The DBA can also validate and use the data in the interim table before actually performing the final step in the redefinition process. A feature that allows DBAs to ensure that the upgrade process will complete successfully.

Here is a list of the tasks that the DBA can perform with online table redefinition:



Restrictions for Redefining Tables Online

There are several restrictions that you should recognize before attempting to redefine a table online:



The DBMS_REDEFINITION PL/SQL Package

To perform online data reorganization and data redefinition, Oracle9i includes a new package called DBMS_REDEFINITION. Here is a brief listing the procedures included in this new package:



Create User and Initial Objects

This section provides the DDLs used to create a demo user and initial database objects. For this article, we will be reorganizing the EMP table.

Create User and Initial Objects
CONNECT sys/change_on_install as sysdba

/*
 * +-------------------------------------------+
 * | CREATE DEMO USER:    reorg                |
 * +-------------------------------------------+
 */

prompt Creating Demo User...

DROP USER reorg CASCADE
/

CREATE USER reorg IDENTIFIED BY reorg
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
/

GRANT dba, resource, connect TO reorg
/

GRANT execute on DBMS_REDEFINITION TO reorg
/


/*
 * +-------------------------------------------+
 * | CONNECT AS DEMO USER:    reorg            |
 * +-------------------------------------------+
 */

prompt Connect as the test user. Default REORG...

CONNECT reorg/reorg


/*
 * +-------------------------------------------+
 * | CREATE TABLE:   dept                      |
 * +-------------------------------------------+
 */

CREATE TABLE dept (
    deptno   NUMBER
  , dname    VARCHAR2(20)
  , loc      VARCHAR2(20)
);

ALTER TABLE dept
ADD CONSTRAINT dept_pk PRIMARY KEY(deptno);

INSERT INTO dept VALUES (1, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (2, 'RESEARCH',   'DALLAS');
INSERT INTO dept VALUES (3, 'SALES',      'CHICAGO');
INSERT INTO dept VALUES (4, 'OPERATIONS', 'BOSTON');

COMMIT;


/*
 * +-------------------------------------------+
 * | CREATE TABLE:   emp                       |
 * +-------------------------------------------+
 */

CREATE TABLE emp (
    empno     NUMBER
  , name      VARCHAR2(20)
  , deptno    NUMBER
  , salary    NUMBER
);

ALTER TABLE emp
  ADD CONSTRAINT emp_pk PRIMARY KEY(empno);

ALTER TABLE emp
  ADD CONSTRAINT emp_fk1 FOREIGN KEY (deptno) REFERENCES dept(deptno);

CREATE INDEX emp_deptno_idx1 ON emp(deptno);


/*
 * +-------------------------------------------+
 * | CREATE TABLE:   audit_emp                 |
 * +-------------------------------------------+
 */

CREATE TABLE audit_emp (
    action             VARCHAR2(10)
  , log_update_time    DATE
);


/*
 * +-------------------------------------------+
 * | CREATE TRIGGER:   trg_hiredate_first      |
 * +-------------------------------------------+
 */

CREATE OR REPLACE TRIGGER emp_audit_trg
  BEFORE INSERT OR UPDATE OR DELETE ON emp
  FOR EACH ROW
  BEGIN

    IF INSERTING THEN 
      INSERT INTO audit_emp VALUES ('INSERT', SYSDATE);
    END IF;

    IF UPDATING THEN
      INSERT INTO audit_emp VALUES ('UPDATE', SYSDATE);
    END IF;

    IF DELETING THEN
      INSERT INTO audit_emp VALUES ('DELETE', SYSDATE);
    END IF;

  END;
/


/*
 * +-------------------------------------------+
 * | POPULATE emp TABLE                        |
 * +-------------------------------------------+
 */

BEGIN

  FOR i IN 1 .. 100000 loop

    INSERT INTO emp
      VALUES(
          i
        , DBMS_RANDOM.STRING('a', 10)
        , TRUNC(DBMS_RANDOM.VALUE(1, 5))
        , TRUNC(DBMS_RANDOM.VALUE(19000, 250000))
      );

    IF (MOD(i, 1000) = 0) THEN
      COMMIT;
    END IF;

  END LOOP;

  COMMIT;

END;
/



Initial EMP Table

The remaining sections of this article demonstrates how to use the new online data reorganization and redefinition process in Oracle9i. At this point, you should have already created the demo Oracle user and several database objects to use in our example. After creating the initial objects, we will continue the example by reorganizing the EMP table.

Here is an overview of the initial structure of the EMP table:


SQL> SELECT object_name, object_type, status, object_id, data_object_id
  2  FROM user_objects;

OBJECT_NAME          OBJECT_TYPE        STATUS   OBJECT_ID DATA_OBJECT_ID
-------------------- ------------------ ------- ---------- --------------
AUDIT_EMP            TABLE              VALID        31772          31772
DEPT                 TABLE              VALID        31767          31767
DEPT_PK              INDEX              VALID        31768          31768
EMP                  TABLE              VALID        31769          31769
EMP_AUDIT_TRG        TRIGGER            VALID        31773
EMP_DEPTNO_IDX1      INDEX              VALID        31771          31771
EMP_PK               INDEX              VALID        31770          31770

7 rows selected.



Determine if Table is a Candidate for Online Reorganization

SQL> connect reorg/reorg
Connected.

SQL> exec dbms_redefinition.can_redef_table('REORG', 'EMP');

PL/SQL procedure successfully completed.



Create Empty Interim Table Reflecting the Final Structure

Here is what the new structure of the EMP table will look like after it undergoes the changes we will be making in this article:


CREATE TABLE int_emp (
    empno          NUMBER
  , ename          VARCHAR2(20)
  , deptno         NUMBER
  , salary         NUMBER
  , bonus_salary   NUMBER
) PARTITION BY LIST (deptno) (
    partition p1 values (1)
  , partition p2 values (2)
  , partition p3 values (3)
  , partition p4 values (4)
);


SQL> SELECT object_name, object_type, status, object_id, data_object_id
  2  FROM user_objects;

OBJECT_NAME          OBJECT_TYPE        STATUS   OBJECT_ID DATA_OBJECT_ID
-------------------- ------------------ ------- ---------- --------------
AUDIT_EMP            TABLE              VALID        31772          31772
DEPT                 TABLE              VALID        31767          31767
DEPT_PK              INDEX              VALID        31768          31768
EMP                  TABLE              VALID        31769          31769
EMP_AUDIT_TRG        TRIGGER            VALID        31773
EMP_DEPTNO_IDX1      INDEX              VALID        31771          31771
EMP_PK               INDEX              VALID        31770          31770
INT_EMP              TABLE PARTITION    VALID        31775          31775
INT_EMP              TABLE PARTITION    VALID        31776          31776
INT_EMP              TABLE PARTITION    VALID        31777          31777
INT_EMP              TABLE PARTITION    VALID        31778          31778
INT_EMP              TABLE              VALID        31774

12 rows selected.



Start the Reorganization Process


NOTE: The user who will be performing the reorganization will require the following privileges:

  • CREATE ANY TABLE
  • ALTER ANY TABLE
  • DROP ANY TABLE
  • LOCK ANY TABLE
  • SELECT ANY TABLE




exec DBMS_REDEFINITION.START_REDEF_TABLE ( -
      'REORG' -
    , 'EMP' -
    , 'INT_EMP' -
    , 'empno empno, name ename, deptno deptno, salary salary, salary*1.10 bonus_salary');

PL/SQL procedure successfully completed.




SQL> SELECT sql_text FROM v$sqlarea WHERE sql_text LIKE '%INT_EMP%';

SQL_TEXT
---------------------------------------------------------------------------------------
truncate table "REORG"."INT_EMP" purge snapshot log

DELETE FROM "REORG"."INT_EMP" SNAP$ WHERE "EMPNO" = :1

SELECT sql_text FROM v$sqlarea WHERE sql_text LIKE '%INT_EMP%'
INSERT INTO "REORG"."INT_EMP"  ("EMPNO","ENAME","DEPTNO","SALARY","BONUS_SALARY"
) VALUES (:1,:2,:3,:4,:5)

UPDATE "REORG"."INT_EMP" SET "EMPNO" = :1,"ENAME" = :2,"DEPTNO" = :3,"SALARY" =
:4,"BONUS_SALARY" = :5 WHERE "EMPNO" = :1

BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (        'REORG'      , 'EMP'      , '
INT_EMP'      , 'empno empno, name ename, deptno deptno, salary salary, salary*1
.10 bonus_salary'); END;

INSERT INTO "REORG"."INT_EMP"("EMPNO","ENAME","DEPTNO","SALARY","BONUS_SALARY")
SELECT "EMP"."EMPNO","EMP"."NAME","EMP"."DEPTNO","EMP"."SALARY","EMP"."SALARY"*1
.10 FROM "REORG"."EMP" "EMP"

INSERT /*+ APPEND */ INTO "REORG"."INT_EMP"("EMPNO","ENAME","DEPTNO","SALARY","B
ONUS_SALARY") SELECT "EMP"."EMPNO","EMP"."NAME","EMP"."DEPTNO","EMP"."SALARY","E
MP"."SALARY"*1.10 FROM "REORG"."EMP" "EMP"


8 rows selected.




SQL> SELECT object_name, object_type, status, object_id, data_object_id
  2  FROM user_objects;

OBJECT_NAME          OBJECT_TYPE        STATUS   OBJECT_ID DATA_OBJECT_ID
-------------------- ------------------ ------- ---------- --------------
AUDIT_EMP            TABLE              VALID        31772          31772
DEPT                 TABLE              VALID        31767          31767
DEPT_PK              INDEX              VALID        31768          31768
EMP                  TABLE              VALID        31769          31769
EMP_AUDIT_TRG        TRIGGER            VALID        31773
EMP_DEPTNO_IDX1      INDEX              VALID        31771          31771
EMP_PK               INDEX              VALID        31770          31770
INT_EMP              TABLE PARTITION    VALID        31775          31781
INT_EMP              TABLE PARTITION    VALID        31776          31782
INT_EMP              TABLE PARTITION    VALID        31777          31783
INT_EMP              TABLE PARTITION    VALID        31778          31784
INT_EMP              TABLE              VALID        31774
MLOG$_EMP            TABLE              VALID        31779          31779
RUPD$_EMP            TABLE              VALID        31780

14 rows selected.

NOTE: Two tables were created - (1) A permanent table MLOG$_EMP which is a snapshot log on the EMP table to store all DML performed on the EMP table once the START_REDEF_TABLE procedures is launced and (2) a temporary table RUPD$_EMP of session duration.




SQL> select count(1) from int_emp;

  COUNT(1)
----------
    100000


SQL> select count(1) from mlog$_emp;

  COUNT(1)
----------
         0


SQL> select count(1) from rupd$_emp;

  COUNT(1)
----------
         0


SQL> select count(1) from emp;

  COUNT(1)
----------
    100000


SQL> select action, count(1) from audit_emp group by action;

ACTION       COUNT(1)
---------- ----------
INSERT         100000


SQL> select mview_name, container_name, build_mode
  2  from user_mviews;

MVIEW_NAME                     CONTAINER_NAME                 BUILD_MOD
------------------------------ ------------------------------ ---------
INT_EMP                        INT_EMP                        PREBUILT


SQL> set long 8000
SQL> select query from user_mviews;

QUERY
--------------------------------------------------------------------------------
select empno empno, name ename, deptno deptno, salary salary, salary*1.10 bonus_
salary from "REORG"."EMP"



Create Indexes, Constraints, Triggers, and Grants on Interim Table

After starting the redefinition process, you can now create any required indexes, constraints, triggers, and grants on the interim table. All referential integrity constraints on teh interim table must be created with the DISABLE option. Upon completion of the redefinition process, Oracle will automatically ENABLE these constraints.

/*
 * -----------------------------------
 * CREATE CONSTRAINTS ON INTERIM TABLE
 * -----------------------------------
 */

SQL> ALTER TABLE int_emp
  2  ADD CONSTRAINT int_emp_pk PRIMARY KEY (empno);

Table altered.


SQL> ALTER TABLE int_emp ADD CONSTRAINT
  2  int_emp_fk1 FOREIGN KEY (deptno) REFERENCES dept(deptno);

Table altered.


SQL> ALTER TABLE int_emp MODIFY CONSTRAINT int_emp_fk1
  2  DISABLE KEEP INDEX;

Table altered.


SQL> SELECT constraint_name, constraint_type, status
  2  FROM user_constraints;

CONSTRAINT_NAME      C TABLE_NAME STATUS
-------------------- - ---------- --------
DEPT_PK              P DEPT       ENABLED
EMP_PK               P EMP        ENABLED
EMP_FK1              R EMP        ENABLED
INT_EMP_PK           P INT_EMP    ENABLED
INT_EMP_FK1          R INT_EMP    DISABLED



/*
 * -------------------------------
 * CREATE INDEXES ON INTERIM TABLE
 * -------------------------------
 */

SQL> CREATE INDEX int_emp_deptno_idx1 ON int_emp (deptno);

Index created.


SQL> select index_name, table_name, column_name from user_ind_columns;

INDEX_NAME                     TABLE_NAME COLUMN_NAME
------------------------------ ---------- ---------------
DEPT_PK                        DEPT       DEPTNO
EMP_PK                         EMP        EMPNO
EMP_DEPTNO_IDX1                EMP        DEPTNO
INT_EMP_PK                     INT_EMP    EMPNO
INT_EMP_DEPTNO_IDX1            INT_EMP    DEPTNO



/*
 * --------------------------------
 * CREATE TRIGGERS ON INTERIM TABLE
 * --------------------------------
 *
 * We will deliberately create a trigger on the new interim 
 * table that will insert two records into the audit_table 
 * instead of two to show that after the redefinition process, 
 * the appropriate trigger (the one we are defining here) will be 
 * used.
 */

CREATE OR REPLACE TRIGGER int_emp_audit_trg
  BEFORE INSERT OR UPDATE OR DELETE ON int_emp
  FOR EACH ROW
  BEGIN

    IF INSERTING THEN 
      INSERT INTO audit_emp VALUES ('INSERT1', SYSDATE);
      INSERT INTO audit_emp VALUES ('INSERT2', SYSDATE);
    END IF;

    IF UPDATING THEN
      INSERT INTO audit_emp VALUES ('UPDATE1', SYSDATE);
      INSERT INTO audit_emp VALUES ('UPDATE2', SYSDATE);
    END IF;

    IF DELETING THEN
      INSERT INTO audit_emp VALUES ('DELETE1', SYSDATE);
      INSERT INTO audit_emp VALUES ('DELETE2', SYSDATE);
    END IF;

  END;
/


SQL> select trigger_name, table_name, status from user_triggers;

TRIGGER_NAME         TABLE_NAME    STATUS
-------------------- ------------- --------
EMP_AUDIT_TRG        EMP           ENABLED
INT_EMP_AUDIT_TRG    INT_EMP       ENABLED



/*
 * ------------------------------
 * CREATE GRANTS ON INTERIM TABLE
 * ------------------------------
 */

SQL> grant select, update, delete on int_emp to scott;

Grant succeeded.


SQL> select * from user_tab_privs_made;

GRANTEE  TABLE_NAME GRANTOR  PRIVILEGE  GRANTABLE HIERARCHY
-------- ---------- -------- ---------- --------- ---------
SCOTT    INT_EMP    REORG    DELETE     NO        NO
SCOTT    INT_EMP    REORG    SELECT     NO        NO
SCOTT    INT_EMP    REORG    UPDATE     NO        NO



Simulate Some DML Activity on Source Table

Keep in mind that the DBA has control as to when to perform the switch and complete the redefinition process. During this period, the DBA can periodically synchronize the interim table with the original (source) table if there is a lot of DML being performed on the source table.
SQL> delete from emp where empno = 1;

1 row deleted.


SQL> commit;

Commit complete.


SQL> select count(1) from emp;

  COUNT(1)
----------
     99999


SQL> select count(1) from int_emp;

  COUNT(1)
----------
    100000


SQL> select count(1) from mlog$_emp;

  COUNT(1)
----------
         1


SQL> select action, count(1) from audit_emp group by action;

ACTION       COUNT(1)
---------- ----------
DELETE              1
INSERT         100000




Now let's resync the interim table with the source table:

exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE ( -
      'REORG' -
    , 'EMP' -
    , 'INT_EMP');

PL/SQL procedure successfully completed.


SQL> select count(1) from emp;

  COUNT(1)
----------
     99999


SQL> select count(1) from int_emp;

  COUNT(1)
----------
     99999


SQL> select count(1) from mlog$_emp;

  COUNT(1)
----------
         0


SQL> select action, count(1) from audit_emp group by action;

ACTION       COUNT(1)
---------- ----------
DELETE              1
INSERT         100000



Complete the Redefinition Process

The DBA can use the dbms_redefinition.finish_redef_table() procedure in order to complete the redefinition process. This is the only step where the table is locked in exclusive mode for the brief moment of time. During this small period of time, the table will be inaccessible to DML access from users.

With this step, the original table is redefined with all the characteristics, indexes, constraints, triggers, and grants that were defined on the interim table. Also at this time, all referential constraints will be enabled.

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE ( -
      'REORG' -
    , 'EMP' -
    , 'INT_EMP');

PL/SQL procedure successfully completed.


SQL> DELETE FROM emp WHERE empno = 2;

1 row deleted.


SQL> commit;

Commit complete.

SQL> -- We can see the new trigger is being used!
SQL> select action, count(1) from audit_emp group by action;

ACTION       COUNT(1)
---------- ----------
DELETE              1
DELETE1             1
DELETE2             1
INSERT         100000

  



The Cleanup Process

At this point, the new EMP is in place and being accessed by users. The interim table, INT_EMP has been switched to the EMP and visa versa.

The DBA can now drop the original EMP table, which is now renamed to INT_EMP:

SQL> DROP TABLE int_emp;

Table dropped.



/*
 * ----------------------------
 * RENAME CONSTRAINTS / INDEXES
 * ----------------------------
 */

SQL> SELECT object_name, object_type, status, object_id, data_object_id
  2  FROM user_objects;

OBJECT_NAME          OBJECT_TYPE        STATUS   OBJECT_ID DATA_OBJECT_ID
-------------------- ------------------ ------- ---------- --------------
AUDIT_EMP            TABLE              VALID        31839          31839
DEPT                 TABLE              VALID        31834          31834
DEPT_PK              INDEX              VALID        31835          31835
EMP                  TABLE PARTITION    VALID        31842          31848
EMP                  TABLE PARTITION    VALID        31843          31849
EMP                  TABLE PARTITION    VALID        31844          31850
EMP                  TABLE PARTITION    VALID        31845          31851
EMP                  TABLE              VALID        31841
INT_EMP_AUDIT_TRG    TRIGGER            VALID        31854
INT_EMP_DEPTNO_IDX1  INDEX              VALID        31853          31853
INT_EMP_PK           INDEX              VALID        31852          31852

11 rows selected.


SQL> SELECT constraint_name, constraint_type, index_name, status
  2  FROM user_constraints;

CONSTRAINT_NAME            C INDEX_NAME  STATUS
-------------------------- - ----------- --------
DEPT_PK                    P DEPT_PK     ENABLED
INT_EMP_PK                 P INT_EMP_PK  ENABLED
INT_EMP_FK1                R             ENABLED

3 rows selected.


SQL> ALTER TABLE emp RENAME CONSTRAINT int_emp_pk TO emp_pk;

Table altered.


SQL> ALTER TABLE emp RENAME CONSTRAINT int_emp_fk1 TO emp_fk1;

Table altered.


SQL> ALTER INDEX int_emp_pk RENAME TO emp_pk;

Index altered.


SQL> ALTER INDEX int_emp_deptno_idx1 RENAME TO emp_deptno_idx1;

Index altered.


SQL> ALTER TRIGGER int_emp_audit_trg RENAME TO emp_audit_trg;

Trigger altered.


SQL> SELECT object_name, object_type, status, object_id, data_object_id
  2  FROM user_objects;

OBJECT_NAME          OBJECT_TYPE        STATUS   OBJECT_ID DATA_OBJECT_ID
-------------------- ------------------ ------- ---------- --------------
AUDIT_EMP            TABLE              VALID        31839          31839
DEPT                 TABLE              VALID        31834          31834
DEPT_PK              INDEX              VALID        31835          31835
EMP                  TABLE PARTITION    VALID        31842          31848
EMP                  TABLE PARTITION    VALID        31843          31849
EMP                  TABLE PARTITION    VALID        31844          31850
EMP                  TABLE PARTITION    VALID        31845          31851
EMP                  TABLE              VALID        31841
EMP_AUDIT_TRG        TRIGGER            VALID        31854
EMP_DEPTNO_IDX1      INDEX              VALID        31853          31853
EMP_PK               INDEX              VALID        31852          31852

11 rows selected.


SQL> SELECT constraint_name, constraint_type, index_name, status
  2  FROM user_constraints;

CONSTRAINT_NAME      C INDEX_NAME       STATUS
-------------------- - ---------------- --------
DEPT_PK              P DEPT_PK          ENABLED
EMP_PK               P EMP_PK           ENABLED
EMP_FK1              R                  ENABLED

3 rows selected.



Aborting the Process

Once the DBMS_REDEFINITION.START_REDEF_TABLE() procedure has been started, the DBA can interrupt the process using the following:
SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE('REORG', 'EMP', 'INT_EMP');


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 20-Jan-2006 16:59:57 EST
Page Count: 29644