Moving table from one Tablespace to other tablespace in oracle.

When objects are created in diffrent tablespace or to remove fragmentation in tablespace. DBA plan for moving one T/S to other tablespace.

Best way to Full schema movement to other tablespace to other tablespace carry out exp/imp or expdp/impdp or alter table move.

exp/imp
===============

select userenv('language') from dual ;
AMERICAN_AMERICA.AL32UTF8

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

exp file=meta_data.dmp log=meta_data_exp.log owner=TEST buffer=500000

imp file=meta_data.dmp log=meta_data_imp.log indexfile=meta_data_required.sql ignore=y commit=y fromuser=TEST touser=TEST buffer=50000 rows=n

In meta_data_required.sql file all metadata of objects is captured , Edit file change tablespace name to other tablespace name and remove REM .

drop user TEST ;

create user TEST and provide required privilege and quota on new tablespace.


connect test/test
sql > @meta_data_required.sql

Above script will create all the objects in new tablespace.

verify tablespace used.

select distinct(tablespace_name) from dba_segments where owner='TEST';

once object creation is completed start import.

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

imp file=meta_data.dmp log=meta_data_imp_final.log ignore=y commit=y fromuser=TEST touser=TEST buffer=500000


In case of issue or to reduce down time.
===========================================

DBA team can plan table by table movement by using

alter table move tablespace ;

alter table move tablespace parallel 8;


Above alter table movment having below disadvantages.

1. After movemement of tables depending indexes will become unusable , need to rebuild. this operation is additional overhead and
generates high archive log generation.
2. LONG objects related tables movement not possible.

to speed up table movement use parallel hint as below.


alter table move tablespace parallel 8;

once movement is completed change degree of table to 1.

alter table degree 1;


Rebuild all related indexes using

select index_name , index_type , status, degree from dba_indexes where table_name='TEST_TABLE' and owner='TEST';

alter index index_name rebuild ;

else

alter index index_name rebuild parallel 8;

once index rebuild is done

select index_name , index_type , status, degree from dba_indexes where table_name='TEST_TABLE' and owner='TEST';

alter index index_name noparallel;