ORACLE - DBA Tips Corner

Oracle DBA Tips Corner

Return to the Oracle DBA Tips Corner.

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

by Jeff Hunter, Sr. Database Administrator

Contents

Overview
New Online Capabilities
Restrictions for Redefining Tables Online
The DBMS_REDEFINITION PL/SQL Package
Create User and Initial Objects
Initial EMP Table
Determine if Table is a Candidate for Online Reorganization
Create Empty Interim Table Reflecting the Final Structure
Start the Reorganization Process
Create Indexes, Constraints, Triggers, and Grants on Interim Table
Simulate Some DML Activity on Source Table
Complete the Redefinition Process
The Cleanup Process
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:

Modify the physical attributes or storage parameters of a table
Add support for parallel queries
Add or drop partitioning support for a table
Move a heap organized table or an index organized table (IOT) to a different tablespace
Recreate a heap organized table or an index organized table (IOT) to reduce fragmentation
Change a heap organized table to an index organized table (IOT) and vice versa
Add, drop, or rename columns in a table
Transform data in a table

Restrictions for Redefining Tables Online

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

The table being redefined must have a primary key.
The source table (the one being redefined) and the target table must have the same primary key column.
The table cannot be part of a cluster.
The table cannot be an overflow table for an IOT.
The table cannot have user-defined types.
The table cannot have a BFILE column or a LONG column. The table may, however, contain LOB columns.
The table cannot belong to the SYS or SYSTEM schema.
If a new column is being added, it cannot be defined NOT NULL.
The table may not have materialized views or have materialized view logs defined on the table.
Both tables in the refefinition process must belong to the same schema.

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:

DBMS_REDEFINITION.ABORT_REDEF_TABLE()

 Argument Name   Type              In/Out Default?
 --------------- ----------------- ------ --------
 UNAME           VARCHAR2                IN
 ORIG_TABLE      VARCHAR2                IN
 INT_TABLE       VARCHAR2                IN

DBMS_REDEFINITION.CAN_REDEF_TABLE()

 Argument Name   Type              In/Out Default?
 --------------- ----------------- ------ --------
 UNAME           VARCHAR2          IN
 TNAME           VARCHAR2          IN
 OPTIONS_FLAG    BINARY_INTEGER    IN     DEFAULT

DBMS_REDEFINITION.FINISH_REDEF_TABLE()

 Argument Name   Type              In/Out Default?
 --------------- ----------------- ------ --------
 UNAME           VARCHAR2          IN
 ORIG_TABLE      VARCHAR2          IN
 INT_TABLE       VARCHAR2          IN

DBMS_REDEFINITION.START_REDEF_TABLE()

 Argument Name   Type              In/Out Default?
 --------------- ----------------- ------ --------
 UNAME           VARCHAR2          IN
 ORIG_TABLE      VARCHAR2          IN
 INT_TABLE       VARCHAR2          IN
 COL_MAPPING     VARCHAR2          IN     DEFAULT
 OPTIONS_FLAG    BINARY_INTEGER    IN     DEFAULT

DBMS_REDEFINITION.SYNC_INTERIM_TABLE()

 Argument Name   Type              In/Out Default?
 --------------- ----------------- ------ --------
 UNAME           VARCHAR2          IN
 ORIG_TABLE      VARCHAR2          IN
 INT_TABLE       VARCHAR2          IN

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:

Initial structure of the emp table:

SQL> desc emp

 Name           Null?    Type
 -------------- -------- --------------
 EMPNO          NOT NULL NUMBER
 NAME                    VARCHAR2(20)
 DEPTNO                  NUMBER
 SALARY                  NUMBER

Initial constraints on table emp:

EMP_PK:           Primary key with a UNIQUE index EMP_PK
EMP_FK1:          Foreign key referencing the DEPT 
                  primary key (DEPT_PK)

Initial indexes on table emp:

EMP_PK:           Defined on the primary key column EMPNO
EMP_DEPTNO_IDX1:  Non-unique index on the foreign key column DEPTNO

Initial triggers on table emp:

EMP_AUDIT_TRG:    Trigger that inserts a single record into the
                  AUDIT_EMP for each INSERT/UPDATE/DELETE that 
                  occurs on the EMP table.

Initial rows in the emp table before reorganization:

SQL> select count(1) from emp;

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

Initial rows in the audit_emp table before reorganization:

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

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

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:

Partition the EMP table:

The new EMP table will go from a non-partitioned table
to a partitioned table; partitioned by list on the DEPTNO column.

Change column name:

Change the column name of EMP.NAME to EMP.ENAME.

Add a new column:

Add a new column named BONUS_SALARY. This
column will take what is currently in the SALARY column
for the user and increase it by 10%.

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');

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: 9788