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;
Posts
