Home > administration > reorganize tables online with dbms_redefinition

reorganize tables online with dbms_redefinition

Sometimes it’s neccessary to change table structures or to rebuild tables.

Unfortunately you wont get a downtime to do so…. even business as usual ūüėČ

With the package dbms_redefinition you can modify your tables online (with only a view exclusive locks)

short summary :

1. check for redefinition

2. create a interim table (with your new definition)

3. duplicate original table to interim table

in background a mlog-table (if rowid is used) will be updated while both tables are “in use”

4. create dependent objects (e.g. indexes, views …)

5. resync tables

6. redefinition finish

7. drop interim table

9. possible problems

Step by step  (tested in 10.2)

Following privileges are needed to run this package:

execute privilege to DBMS_REDEFINITION

create any table

alter any table

drop any table

lock any table

select any table

1. check wether redefinition is possible

by primary key  

¬†exec dbms_redefinition.can_redef_table(‘SCOTT’,’EMP’,dbms_redefinition.cons_use_pk);¬†

by rowid    

exec dbms_redefinition.can_redef_table(‘SCOTT’,’EMP’,dbms_redefinition.cons_use_rowid);

-> creates a mlog$_table  (in schema-default-tablespace)

2. create an empty interim table

If desired create this table according your new requirementsStruktur gem√§√ü den neuen Anforderungen anpassen, sofern gew√ľnscht

create table EMP_tmp tablespace users as select * from EMP where 1=2;   

3. starting redefinition (rowid-method)

exec dbms_redefinition.start_redef_table(‘SCOTT’,’EMP’,’EMP_tmp’,NULL,dbms_redefinition.cons_use_rowid);

-> creates a snapshot of emp, a mlog$_xxx table and a  temporary index on EMP_tmp (in the schema-default-tablespace)

this requires a short exclusive lock !

you can also convert long to lob

exec dbms_redefinition.start_redef_table(‘SCOTT’,’EMP’,’EMP_tmp’,’col1 col1, testlob testlob, to_lob(testlong) testlong’,dbms_redefinition.cons_use_rowid);

4. create dependend objects (e.g. indexes, views….)

    num_errors pls_integer;
¬†¬†¬† dbms_redefinition.copy_table_dependents(‘SCOTT’,’EMP’,’EMP_tmp’,DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);

These objects will be stored in schema default tablespace

-> skipping this step, will cause no indexes etc will be created !

show errors:

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;    

5. resync interim with original table

using interim table parallel to the original table, all changes will be tracked in the mlog$-tableto mimimize this table a resync should be done…

exec dbms_redefinition.sync_interim_table(‘SCOTT’,’EMP’,’EMP_tmp’);¬†¬†¬†¬†

6. finish redefinition

exec dbms_redefinition.finish_redef_table(‘SCOTT’,’EMP’,’EMP_tmp’);¬†

-> original table will be change to _tmp (see 2.) and interim table activated

again, a short exclusive lock neccessary

7. drop interim table

drop table EMP_tmp purge;  

to abort the redefinition process

exec dbms_redefinition.abort_redef_table(‘SCOTT’,’EMP’,’EMP_tmp’);


drop table EMP_tmp purge; 

If an exclusive lock is not possible, the session will wait 

8. possible problems

SQL> exec dbms_redefinition.can_redef_table(‘SCOTT’,’EMP’,dbms_redefinition.cons_use_pk);

¬†*ERROR at line 1:ORA-12091: cannot online redefine table “SCOTT”.”EMP” with materialized views ORA-06512: at “SYS.DBMS_REDEFINITION”, line 137 ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1479 ORA-06512: at line 1


Restrictions for Online Redefinition of Tables (from  Oracle 11gR1 Dokumentation РAdministrators Guide)

The following restrictions apply to the online redefinition of tables:

* If the table is to be redefined using primary key or pseudo-primary keys (unique keys or constraints with all component columns having not null constraints), then the post-redefinition table must have the same primary key or pseudo-primary key columns. If the table is to be redefined using rowids, then the table must not be an index-organized table.

* Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.

* The overflow table of an index-organized table cannot be redefined online independently.

* Tables with fine-grained access control (row-level security) cannot be redefined online.

* Tables with BFILE columns cannot be redefined online.

* Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.

* On a system with sufficient resources for parallel execution, and in the case where the interim table is not partitioned, redefinition of a LONG column to a LOB column can be executed in parallel, provided that:

o The segment used to store the LOB column in the interim table belongs to a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled.

o There is a simple mapping from one LONG column to one LOB column, and the interim table has only one LOB column.

In the case where the interim table is partitioned, the normal methods for parallel execution for partitioning apply.

* Tables in the SYS and SYSTEM schema cannot be redefined online.

* Temporary tables cannot be redefined.

* A subset of rows in the table cannot be redefined.

* Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.

* If new columns are being added as part of the redefinition and there are no column mappings for these columns, then they must not be declared NOT NULL until the redefinition is complete.

* There cannot be any referential constraints between the table being redefined and the interim table.

* Table redefinition cannot be done NOLOGGING.

* For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties. No horizontal or vertical subsetting is permitted, nor are any column transformations. The only valid value for the column mapping string is NULL.

* Tables with materialized view logs defined on them cannot be redefined online.

* You can convert a VARRAY to a nested table with the CAST operator in the column mapping. However, you cannot convert a nested table to a VARRAY.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: