Moving Schema to another Tablespace using "move" command.

Purpose of Moving?

We move schema or tables to other tablespace in-order to remove fragmentation.

Let’s do this:

We here move the Scott schema which is currently present in users tablespace.

SQL>Create tablespace mytab datafile ‘D:\oracle\product\10.2.0\oradata\mytab.dbf’ size 5m;

Tablespace created

Now check the current tablespace of Scott:

SQL> select owner, tablespace_name from dba_tables where owner='SCOTT';

OWNER                          TABLESPACE_NAME

------------------------------ ------------------------------

SCOTT                          USERS

Set spool on:

SQL> spool  D:\move.sql

SQL> select 'alter table ' || table_name || ' move tablespace mytab ;' from dba_

tables where owner='SCOTT' ;

'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACEMYTAB;'

------------------------------------------------------------------

alter table DEPT move tablespace mytab ;

alter table EMP move tablespace mytab ;

alter table BONUS move tablespace mytab ;

alter table SALGRADE move tablespace mytab ;

alter table TEST_LOGMNR move tablespace mytab ;

SQL> spool off

A script is created as move.sql containing the list of tables to be move.

Now, run this script

SQL>@move.sql

The table move will change the ROWIDs of the table rows, and as such the indexes, which are based on ROWIDs, will become invalid (UNUSABLE).Therfeore, we need to rebuild them too.

An advantage of using the table move procedure is all constraints are preserved.

SQL>spool D:\index.sql

SQL> select 'Alter index ' || index_name || ' rebuild tablespace mytab ;' from dba_indexes where owner='SCOTT' ;

SQL>conn scott/tiger

SQL>@index.sql

Now check the tablespace of user scott

SQL> select owner, tablespace_name from dba_tables where owner='SCOTT';

OWNER                          TABLESPACE_NAME

------------------------------ ------------------------------

SCOTT                          MYTAB

ENJOY:-)