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:-)
