The new PL/SQL procedure REDEF_TABLE in Oracle 12c lets you redefine a table online in a single step. It can do the below changes
- Move table,partition or index to another tablespace
- Compression type change on a table including for table,partition and index
More information on DBMS_REDFINITION can be accessed on Oracle Support : Master Note:Overview of Online Redefinition of Tables (DBMS_REDEFINITION) (Doc ID 1357825.1)
We will now go ahead and move the table HR.EMP to another tablespace called TBS01 online.
EXEC BEGIN DBMS_REDEFINITION.REDEF_TABLE( uname => 'HR', tname => 'EMP', table_part_tablespace => 'TBS01' ); END; /
uname –> Schema Name in which Table resides
tname –> Table Name
table_part_tablespace –> New Tablespace to which table will be moved
index_tablespace => If you need to move the indexes to another table it can be done using an additional arguement
