Oracle online table redefinition

Online table redefinition is a mechanism to modify table structures without affecting its availability. It requires exact same amount of free space and more space is required if we are adding any column to the table. We will use DBMS_REDEFINITION package to redefine the table.

Features:

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

Restrictions:

• It is not possible to redefine Materialised Views (MViews) and tables with MViews or MView Logs defined on them.
• Redefining Temporary and Clustered Tables are not possible.
• Redefining tables with BFILE, LONG or LONG RAW columns are not possible.
• Redefining tables belonging to SYS or SYSTEM are not possible.
• Redefining Object tables are not possible.
• Redefinition cannot be done in NOLOGGING mode (watch out for heavy archiving)
• Redefinition cannot be used to add or remove rows from a table

For Testing purpose we have created table TAB_CUSTOMER in DILLI schema.

Grant execute privileges on DBMS_REDEFINITION to user.

grant execute on DBMS_REDEFINITION to dilli;

grant ALTER ANY TABLE, CREATE ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE, CREATE ANY INDEX, CREATE ANY SEQUENCE, CREATE ANY TRIGGER to dilli;

Test if the table can be redefined, Specify source table here

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('DILLI', 'TAB_CUSTOMER',DBMS_REDEFINITION.CONS_USE_ROWID);

we can use following options:

DBMS_REDEFINITION.CONS_USE_ROWID 

Redefine specified table online using rowid

DBMS_REDEFINITION.CONS_USE_PK.

Redefine specified table online using Primary Key

Verify table count of table TAB_CUSTOMER and its segment size.

select count(*) from TAB_CUSTOMER;

select segment_name, bytes/power(1024,3) size_in_GB

from user_segments where segment_name='TAB_CUSTOMER';

Create interim table with new structure if required. Oracle truncates data from this table in next step so do not copy data.

create table TAB_CUSTOMER1

as select * from TAB_CUSTOMER

where 1=2;

Start the redefinition. Oracle will copy (and transform) the data from the production table to the interim table.

EXEC DBMS_REDEFINITION.start_redef_table('DILLI', 'TAB_CUSTOMER', 'TAB_CUSTOMER1',options_flag=> DBMS_REDEFINITION.CONS_USE_ROWID);

Sync intermediate changes to interim table (optional)

 EXEC dbms_redefinition.sync_interim_table('DILLI','TAB_CUSTOMER', 'TAB_CUSTOMER1');

Copy dependencies

SET SERVEROUTPUT ON

DECLARE

  l_num_errors PLS_INTEGER;

BEGIN

  DBMS_REDEFINITION.copy_table_dependents(

    uname               => 'DILLI',

    orig_table          => 'TAB_CUSTOMER',

    int_table           => 'TAB_CUSTOMER1',

    copy_indexes        => 1,             -- Default

    copy_triggers       => TRUE,          -- Default

    copy_constraints    => TRUE,          -- Default

    copy_privileges     => TRUE,          -- Default

    ignore_errors       => FALSE,         -- Default

    num_errors          => l_num_errors,

    copy_statistics     => FALSE,         -- Default

    copy_mvlog          => FALSE);        -- Default

    

  DBMS_OUTPUT.put_line('num_errors=' || l_num_errors); 

END;

/

Complete the redefinition process.

exec dbms_redefinition.finish_redef_table('DILLI', 'TAB_CUSTOMER', 'TAB_CUSTOMER1');

select count(*) from TAB_CUSTOMER;

select count(*) from TAB_CUSTOMER1;

select segment_name, bytes/power(1024,3) size_in_GB

from user_segments where segment_name like 'TAB_CUSTOMER%';

Drop original table.

drop table TAB_CUSTOMER1;