Move table to different tablespace through online redefinition

Some times ago, I tested the steps of moving two tables to different tablespace through online redefiniton. I developed the steps based on the Oracle online documentation.

The background is that we need to do an online copy of a production database with subset data to another data center. These two tables are critical and need to be copied over and would be used to build other tables. However, these two tables reside in a tablespace hosting many other tables. Thus to put them into a separate tablespace would make our copy job much easier.

Below describes the steps and commands involved for reference. TAB_MA and TAB_MS are under schema SCH_SO. The tablespace to which we want to move them is called SCH_SO_DATA_R. (Those are not real production table names)

Step:
~~~~~

1. Verify that the table is a candidate for online redefinition

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('sch_so','tab_ma',
dbms_redefinition.cons_use_pk);
END;
/


BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('sch_so','tab_ms',
dbms_redefinition.cons_use_pk);
END;
/


2. Create an interim tables

create table sch_so.tab_ma_int
tablespace sch_so_data_r
as
select * from sch_so.tab_ma
where 1=0;


create table sch_so.tab_ms_int
tablespace sch_so_data_r
as
select * from sch_so.tab_ms
where 1=0;


3. Start the redefinition process.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
'sch_so', 'tab_ma','tab_ma_int',
)
END;
/


BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
'sch_so', 'tab_ms','tab_ms_int',
)
END;
/


Note:
After redefinition finished, checked the segment size of interim tables:

1 select owner, segment_name, tablespace_name, bytes/1024/1024/1024 size_G from dba_segments
2* where tablespace_name='SCH_SO_DATA_R'
SQL> /

OWNER SEGMENT_NAME TABLESPACE_NAME SIZE_G
---------------- ----------------------- ----------------- ----------
SCH_SO TAB_MA_INT SCH_SO_DATA_R 4.9375
SCH_SO TAB_MS_INT SCH_SO_DATA_R 9.1875

time: 20 min

4. Create any triggers, indexes and constraints on the interim tables


5. Optionally, synchronize the interim table hr.int_admin_emp.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('sch_so', 'tab_ms', 'tab_ms_int');
END;
/

6. Complete the redefinition.

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('sch_so', 'tab_ms', 'tab_ms_int');
END;
/


BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('sch_so', 'tab_ma', 'tab_ma_int');
END;
/

Note: The table is locked in the exclusive mode only for a small window toward the end of this step. After this call the table is redefined such that it has all the attributes of the interim table.

7. Drop the interim table.