Script to Move objects from one tablespace to other

There are many occasions where we will move segments from one tablespace to another like reclaim unused space in the segments after a deleting rows.

We can create scripts to move different type of segments from one tablespace to another using following SQL commands.

Here the scripts will be created to move objects (actually segments) from the tablespacce OLD_USERS to NEW_USERS. You can change the tablespace names accordingly.

Moving Tables and Un-partitioned Indexes from one tablespace to another
set lines 200
set pages 0
set feedback off
spool move.sql
select 
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move', ' rebuild' ) ||
' tablespace NEW_USERS' || ';'
from dba_segments
where tablespace_name='OLD_USERS'  
and segment_type in('TABLE','INDEX') order by owner,segment_type desc,segment_name;
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move all tables from one tablespace to another and to rebuild all indexes from one partition to another.
@move.sql

Moving index partitions from one tablespace to another
set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter index '|| owner||'.'||segment_name || ' rebuild partition '|| partition_name ||' tablespace NEW_USERS;' 
from dba_segments where segment_type ='INDEX PARTITION' and tablespace_name='OLD_USERS';
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move or rebuild all index partitions from one tablespace to another
@move.sql

Moving index sub-partitions from one tablespace to another
set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter index '||owner||'.'||segment_name ||' rebuild subpartition ' || partition_name ||' tablespace NEW_USERS;'       
from dba_segments where tablespace_name='OLD_USERS' and segment_type = 'INDEX SUBPARTITION';
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move or rebuild all index subpartition segments from one tablespace to another
@move.sql

Moving IOT index segments from one tablespace to another(When only IOT indexes left on the tablespace)

set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter table '||owner||'.'|| table_name || ' move tablespace NEW_USERS;' from dba_indexes where index_name in (
select  segment_name from dba_segments
where tablespace_name='OLD_USERS' and segment_type='INDEX');
spool off
 
Now edit the file move.sql to remove first few lines which will be the command above and run it to move all IOT segments form one tablespace to another
@move.sql

Moving LOB segments from one tablespace to another
set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter table '|| owner || '.' ||table_name|| ' move LOB ('||COLUMN_NAME ||') STORE AS (tablespace NEW_USERS);'
from DBA_LOBS where tablespace_name='OLD_USERS';
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move all LOB segments from one tablespace to another
@move.sql


There could be special objects like AQ (Advanced Queueing)tables. Read here to lean how to move AQ tables from one tablespace to another.

5 comments:

  1. Please share any thought regarding move segments. Are there any other type of segments which you are struggling with?

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. Nice article..but one clarification in Moving index sub-partitions from one tablespace to another.. your method is using dba_segments. What is the difference if we are using dba_ind_subpartitions.

    ReplyDelete
    Replies
    1. do you see any difference in practical?

      Delete
  4. I tried many scripts but didn't worked for all objects. You can't move clustered objects from one tablespace to another, for that you will have to use expdp. So I will suggest expdp is the best option to move all objects to different tablesapce.

    Below is the command.

    nohup expdp \"/ as sysdba\" DIRECTORY=test_dir DUMPFILE=users.dmp LOGFILE=users.log TABLESPACES=USERS &

    ReplyDelete