Reorganizing a Table using DBMS_REDEFINITION

Posted at Thursday, July 03, 2008

There are many methods to reorganize/defragment a table in Oracle. If you want to avoid downtime, however, your choices are limited to using the 'SHRINK SPACE' feature and/or employing the DBMS_REDEFINITION PL/SQL package.

This post demonstrates the latter method. The DBMS_REDEFINITION package is most commonly used for redefining a table, however can be used to reorganize a table amongst other things. Here is a great article which covers the package in a little more detail: On-line Table Reorganization and Redefinition.

This example rebuilds the OE.ORDER_ITEMS table using DBMS_REDEFINITION.

Collect information about the table's segments prior to the reorg

ANALYZE TABLE oe.order_items COMPUTE STATISTICS;

SELECT avg_row_len, num_rows, chain_cnt

  FROM dba_tables

 WHERE table_name = 'ORDER_ITEMS' AND owner = 'OE';

SELECT ROUND (BYTES / 1024) kb

  FROM dba_segments

 WHERE owner = 'OE' AND segment_name = 'ORDER_ITEMS';

Check whether the table in question violates any redefinition restrictions

EXEC dbms_redefinition.can_redef_table('OE','ORDER_ITEMS', dbms_redefinition.cons_use_pk);

Create the Intermediary Table - in my case, I use the exact same structure

CREATE TABLE OE.ORDER_ITEMS_REDEF

(

  ORDER_ID      NUMBER(12),

  LINE_ITEM_ID  NUMBER(3)                       NOT NULL,

  PRODUCT_ID    NUMBER(6)                       NOT NULL,

  UNIT_PRICE    NUMBER(8,2),

  QUANTITY      NUMBER(8)

) TABLESPACE USERS;

Start the Redefinition Process

EXEC dbms_redefinition.start_redef_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

You will notice that Oracle creates a Materialized View in order to store the table changes, etc.

SELECT COUNT (*)

  FROM oe.mlog$_order_items;

Sync up the Source table with the Intermediary table

EXEC dbms_redefinition.sync_interim_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

Duplicate the table's dependent objects on the Intermediary table

CREATE INDEX oe.item_order_redef_ix ON oe.order_items_redef

(order_id) LOGGING TABLESPACE users;

CREATE INDEX oe.item_product_redef_ix ON oe.order_items_redef

(product_id) LOGGING TABLESPACE users;

CREATE UNIQUE INDEX oe.order_items_redef_pk ON oe.order_items_redef

(order_id, line_item_id) LOGGING TABLESPACE users;

CREATE UNIQUE INDEX oe.order_items_redef_uk ON oe.order_items_redef

(order_id, product_id) LOGGING TABLESPACE users;

ALTER TABLE oe.order_items_redef ADD (

  CONSTRAINT order_items_redef_pk

 PRIMARY KEY (order_id, line_item_id) USING INDEX TABLESPACE USERS);

ALTER TABLE oe.order_items_redef ADD (

  CONSTRAINT order_items_redef_order_id_fk

 FOREIGN KEY (order_id)

 REFERENCES oe.orders (order_id)

 ON DELETE CASCADE DISABLE NOVALIDATE);

ALTER TABLE oe.order_items_redef ADD (

  CONSTRAINT order_items_rdef_product_id_fk

 FOREIGN KEY (product_id)

 REFERENCES oe.product_information (product_id) DISABLE NOVALIDATE);

GRANT SELECT ON oe.order_items_redef TO bi;

GRANT SELECT ON oe.order_items_redef TO pm;

Notice how, I create the foreign key constraints initially as disabled.

To simplify things, I use the following to duplicate any triggers to the Intermediary table

DECLARE

 retval NUMBER(5);

BEGIN

 dbms_redefinition.copy_table_dependents('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF', copy_indexes=>0, copy_triggers=>TRUE, copy_constraints=>FALSE,copy_privileges=>FALSE, num_errors=>retval);

 dbms_output.put_line(retval);

END;

/

Disable any foreign key constraints on the Source table before finishing the table redefinition process

ALTER TABLE oe.order_items DISABLE CONSTRAINT order_items_order_id_fk;



ALTER TABLE oe.order_items DISABLE CONSTRAINT order_items_product_id_fk;

Finish the Redefinition

EXEC dbms_redefinition.finish_redef_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

Drop the Intermediary Table

DROP TABLE oe.order_items_redef;

Rename any dependent objects

ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_rdef_product_id_fk TO order_items_product_id_fk;

ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_redef_order_id_fk TO order_items_order_id_fk;

ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_redef_pk TO order_items_pk;

ALTER INDEX oe.item_order_redef_ix RENAME TO item_order_ix;

ALTER INDEX oe.item_product_redef_ix RENAME TO item_product_ix;

ALTER INDEX oe.order_items_redef_pk RENAME TO order_items_pk;

ALTER INDEX oe.order_items_redef_uk RENAME TO order_items_uk;

At this point, the table should be reorganized/defragmented. Optionally, compare the storage footprint post-redefintion

ANALYZE TABLE oe.order_items COMPUTE STATISTICS;

SELECT avg_row_len, num_rows, chain_cnt

  FROM dba_tables

 WHERE table_name = 'ORDER_ITEMS' AND owner = 'OE';

SELECT ROUND (BYTES / 1024) kb

  FROM dba_segments

 WHERE owner = 'OE' AND segment_name = 'ORDER_ITEMS';