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
