Script to moving objects from one tablespace to another tablespace

Script to moving objects from one tablespace to another tablespace

There are many occasions where we will move segments from one tablespace to another like reclaim unused space in the segments after 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 tablespace USERS to USERS_TEMP. You can change the tablespace names accordingly.

Moving Tables and Un-partitioned Indexes

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 USERS_TEMP' || ';'

from dba_segments

where tablespace_name='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

set lines 200

set pages 0

set feedback off

spool move.sql

select 'alter index '|| owner||'.'||segment_name || ' rebuild partition '|| partition_name ||' tablespace USERS_TEMP;'

from dba_segments where segment_type ='INDEX PARTITION' and tablespace_name='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

set lines 200

set pages 0

set feedback off

spool move.sql

select 'alter index '||owner||'.'||segment_name ||' rebuild subpartition ' || partition_name ||' tablespace USERS_TEMP;'      

from dba_segments where tablespace_name='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 (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 USERS_TEMP;' from dba_indexes where index_name in (

select  segment_name from dba_segments

where tablespace_name='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

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 USERS_TEMP);'

from DBA_LOBS where tablespace_name='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