Online table reorg

DBMS_REDEFINITION package is a very helpful tool to achieve high availability at database level. I always use it to change table properties for my 24/7 databases without downtime. Here is a example of move tablespace of partitioned table with indexes.

AS SYSDBA

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('ODITIO','ABC_HASH');

SQL> grant execute on dbms_redefinition to ODITIO;

Create interim table and indexes

My original table structure was like bellow:

CREATE TABLE ODITIO.ABC_HASH

(id1 number,

id2 number)

PARTITION BY HASH (id1)

( PARTITION p1

TABLESPACE T1,

PARTITION p2

TABLESPACE T2);

CREATE UNIQUE INDEX ODITIO.ABC_HASH_PK

ON ODITIO.ABC_HASH(ID1)

LOCAL (

PARTITION P1

TABLESPACE I1,

PARTITION P2

TABLESPACE I2

);

I wanted to move the table and index to T3 T4 and I3,I4 tablespace.So I Connected as ODITIO user and created interim table and index first with new tablespace.

SQL> set time on

SQL> set timi on

SQL> CREATE TABLE CREATE TABLE ODITIO.ABC_HASH_INTERIME

(id1 number,

id2 number)

PARTITION BY HASH (id1)

( PARTITION p1

TABLESPACE T3,

PARTITION p2

TABLESPACE t4);

SQL> CREATE UNIQUE INDEX ODITIO.ABC_HASH_PK_INTERIME

ON ODITIO.ABC_HASH(ID1)

LOCAL (

PARTITION P1

TABLESPACE I3,

PARTITION P2

TABLESPACE I4);

Start the process

SQL> BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE('ODITIO','ABC_HASH','ABC_HASH_INTERIM',NULL);

END;

Registering dependency

SQL> Begin

DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(

uname=>'ODITIO',

orig_table=>'ABC_HASH',

int_table=>'ABC_HASH_INTERIM',

dep_type=>dbms_redefinition.cons_index,

dep_owner=>'ODITIO',

dep_orig_name=>'ABC_HASH_PK',

dep_int_name=>'ABC_HASH_PK_INTERIM'

);

End;

Copy dependency

SQL> DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

('ODITIO','ABC_HASH','ABC_HASH_INTERIM',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);

END;

Sync changes

SQL> BEGIN

DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ODITIO', 'ABC_HASH','ABC_HASH_INTERIM');

END;

Finish step of the process

SQL> BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE('ODITIO', 'ABC_HASH','ABC_HASH_INTERIM');

END;

If something goes wrong then we can rollback it like bellow.

SQL> Begin

DBMS_REDEFINITION.ABORT_REDEF_TABLE('ODITIO','ABC_HASH','ABC_HASH_INTERIM');

End;

Find the interim constraints

SQL> select 'alter table tmx.'|| table_name ||' drop constraint ' || constraint_name ||'; ' owner,constraint_name,status, constraint_type,table_name,r_owner,r_constraint_name

from all_constraints

where constraint_type='R'

and r_constraint_name in (select constraint_name from all_constraints

where constraint_type in ('P','U') and table_name like 'ABC_HASH_IN%');

Drop interim table

SQL> drop table ABC_HASH_INTERIM;