Oracle Table Space Transfer

--View the table under table space

Select T.table_name,t.tablespace_name from Dba_tables t where T.tablespace_name = ' ${spacename} ' and owner = ' ${owner} ';

--View the index under Table space

Select T.index_name,t.tablespace_name from dba_indexes t where T.tablespace_name = ' ${spacename} ' and owner = ' ${owner} ';

The above statement owner can not add, if there are multiple users in the case can be used as appropriate.

--Migrating a table to a new table space

Select ' ALTER TABLE ' | | T.table_name | | ' Move tablespace ${newspacename} ' from Dba_tables t where T.tablespace_name = ' ${spacename} ' and owner = ' ${owner} ';

--Migrating an index to a new table space

Select ' Alter index ' | | T.index_name | | ' Rebuild tablespace ${newspacename} ' from Dba_indexes t where T.tablespace_name = ' ${spacename} ' and owner = ' ${owner} ' ;

After performing the resulting set of results, the table space migration is complete:

The above statement is purely hand-punched, there are spelling mistakes please forgive me.

Two exceptions occurred during the actual execution:

1. When a database index is migrated, a partially indexed field is LOB type, which causes the error to be executed at the time of migration.

Solution:

Alter Tabe ${table name} mobe LOB (${field name}) STORE as (Tablespace ${newspacename}); Perform the transfer in turn

The 2.rebuild index causes the index to be in the unabled state, and a warning appears when the export is exported to a new database. Index unavailable State

You can dba_indexes a table with a status of unable, and then rebuild the index.

Oracle Table Space Transfer