Site icon EasyOraDBA

Move Table to Another Tablespace Online Oracle Database 12c

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

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

Exit mobile version