On-line Table Reorganization and Redefinition

Tables can be reorganized and redefined (evolved) on-line with the DBMS_REDEFINITION package. The process is similar to on-line rebuilds of indexes, in that the original table is left on-line, while a new copy of the table is built. However, an index index-rebuild is a singular operation, while table redefinition is a multi-step process.

Table redefinition is started by the DBA creating an interim table based on the original table. The interim table can have a different structure than the original table, and will eventually take the original table's place in the database. While the table is redefined, DML operations on the original table are captured in a Materialized View Log table (MLOG$_%). These changes are eventually transformed and merged into the interim table. When done, the names of the original and the interim tables are swapped in the data dictionary. At this point all users will be working on the new table and the old table can be dropped.

Possible applications:

On-line Table Redefinition can be used for:

• Add, remove, or rename columns from a table
• Converting a non-partitioned table to a partitioned table and vice versa
• Switching a heap table to an index organized and vice versa
• Modifying storage parameters
• Adding or removing parallel support
• Reorganize (defragmenting) a table
• Transform data in a table

Restrictions:

• One cannot redefine Materialized Views (MViews) and tables with MViews or MView Logs defined on them.
• One cannot redefine Temporary and Clustered Tables
• One cannot redefine tables with BFILE, LONG or LONG RAW columns
• One cannot redefine tables belonging to SYS or SYSTEM
• One cannot redefine Object tables
• Table redefinition cannot be done in NOLOGGING mode (watch out for heavy archiving)
• Cannot be used to add or remove rows from a table

Using On-line Redefinition from Enterprise Manager:

Oracle Enterprise Manager's REORG Wizard (part of the Tuning pack) allows DBAs to reorganize tables off-line or on-line. If on-line reorganization is chosen, Oracle will make use of the DBMS_REDEFINITION package. Off-line reorganization is quicker than on-line reorganization, but if the system cannot go down, the on-line method will prove valuable.

Execute the following steps to start the REORG Wizard: Start OEM. Click on TOOLS -> TUNING PACK -> REORG WIZARD.

Using On-line Redefinition from SQL*Plus:

A table can be redefined in 7 easy steps from SQL*Plus.

Step 1: Grant privileges:

Grant EXECUTE ON DBMS_REDEFINITION and the following privileges to the user that will do the redefinition: CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE. Look at this example:

SQL> grant execute on dbms_redefinition to scott;
SQL> grant dba to scott;

Note: These are powerful privileges, so remember to revoke them afterwards.

Step 2: Test if the table can be redefined:

Execute the DBMS_REDEFINITION.CAN_REDEF_TABLE procedure to test if a table can be redefined or not. A table qualifies for redefinition if no exceptions are raised. Possible errors:

• ORA-12089 cannot online redefine table with no primary key
• ORA-12091: cannot online redefine table "SCOTT"."EMP" with materialized views
• ORA-00942: table or view does not exist

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('scott', 'emp', DBMS_REDEFINITION.CONS_USE_PK);

Note: The redefinition process can be based on a Primary Key or ROWID. Two constants are defined for this purpose: DBMS_REDEFINITION.CONS_USE_PK (the default) and DBMS_REDEFINITION.CONS_USE_ROWID. One can also use the value "1" for the primary key method, and "2" to indicate the rowid method. This is required so Oracle will know how to create the mview log: WITH ROWID, or WITH PRIMARY KEY.

Step 3: Create an interim table with new structure

Create an interim table with the new table structure. Define a primary key if you want to use the primary key method (DBMS_REDEFINITION.CONS_USE_PK). This is not required for the rowid method.

Oracle will TRUNCATE the interim table in step 4; so, do not add any data to it yet. Avoid adding constraints and indexes at this stage (for best performance).

Step 4: Start the redefinition

During this phase Oracle will copy (and transform) the data from the production table to the interim table. Oracle will also create a materialized view (snapshot) log on the table to track DML changes.

SQL> exec dbms_redefinition.start_redef_table('scott', 'emp', 'emp_work', -
>         'emp_id emp_id, ename ename, salary salary', -
>         DBMS_REDEFINITION.CONS_USE_PK);

Note parameter 4: mapping for the old table's columns to the new table's columns. This can be left out if the columns are the same.

Step 5: Sync intermediate changes to interim table (optional)

This step will apply changes captured in the materialized view log to the interim table. Perform this step frequently for high transaction tables.

SQL> exec dbms_redefinition.sync_interim_table('scott', 'emp', 'emp_work');

Step 6: Create indexes, constraints and triggers on the interim table

Note that you cannot use the same names for indexes and constraints. Foreign key constraints must be created DISABLED (Oracle will enable them in step 6).

Step 7: Complete the redefinition process

During this step Oracle will lock both tables in exclusive mode, swap the names of the two tables in the data dictionary, and enable all foreign key constraints. Remember to drop the original table afterwards. One can also consider renaming the constraints back to their original names (e.g.: alter table EMP rename constraint SYS_C001806 to emp_fk).

SQL> exec dbms_redefinition.finish_redef_table('scott', 'emp', 'emp_work');

Optional step to Abort Redefinition:

Redefinition can be aborted at any stage by calling the DBMS_REDEFINITION.ABORT_REDEF_TABLE procedure. This will drop the mview log on the production table. Note that you need to abort before trying again, otherwise you will get an ORA-12091 error as described in step 2.

Examples:

Example 1: On-line Table Reorg using the rowid method

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('scott', 'emp', 2);  -- 2=rowid
CREATE TABLE emp_work AS SELECT * FROM emp WHERE 1=2;
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('scott', 'emp', 'emp_work', NULL, 2);
ALTER TABLE emp ADD PRIMARY KEY (empno);
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'emp', 'emp_work');
DROP TABLE emp_work;

Example 2: Redefine a table using the primary key method

-- Create a new table with primary key...
CREATE TABLE myemp (
	empid  NUMBER PRIMARY KEY,
	ename  VARCHAR2(30),
	salary NUMBER(8,2),
	deptno NUMBER);
insert into myemp values (1, 'Frank', 15000, 10);
insert into myemp values (2, 'Willie',  10000, 20);
create index myemp_idx on myemp (ename);

-- Test if redefinition is possible...
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('scott', 'myemp');

-- Create new empty interim table...
CREATE TABLE myemp_work (
	emp#   NUMBER PRIMARY KEY,	-- Change emp_id to emp#
	ename    VARCHAR2(30),
	salary   NUMBER(8,2),		-- We will increase salary by 10%
	deptno   NUMBER)
   PARTITION BY LIST (deptno) (  	-- Add list partitioning
	PARTITION p10 VALUES (10), 
	PARTITION p20 VALUES (20), 
	PARTITION p30 VALUES (30,40));

-- Create a transformation function...
CREATE FUNCTION raise_sal (salary NUMBER) RETURN NUMBER AS
BEGIN
 return salary + salary*0.10; 
END;
/

-- Start the redefinition process
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('scott', 'myemp', 'myemp_work', -
	'empid emp#, ename ename, raise_sal(salary) salary, deptno deptno', -
	DBMS_REDEFINITION.CONS_USE_PK);

-- Apply captured changed to interim table
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('scott', 'myemp', 'myemp_work');

-- Add constraints, indexes, triggers, grants on interim table...
create index myempidx2 on myemp_work (ename);

-- Finish the redefinition process...
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'myemp', 'myemp_work');

-- Cleanup
DROP TABLE myemp_work;
DROP FUNCTION raise_sal;

References:

• Oracle9i Database Administrator's Guide Release 2 (9.2) Chapter 15: Managing Tables