Move all database objects from one tablespace to another

articles: 

Sometime is necessary to move all database objects from one tablespace to another.

Basically are tables, indexes and lobs.

This script permit move database objects from one user and/or tablespace to another:

set echo off
set heading off

var tbs_source varchar2;
var tbs_dest varchar2;
var schema_user varchar2;

-- '' if it isn't relevant.
exec :tbs_source  := 'SOURCE_TBS';
-- '' if it isn't relevant.
exec :schema_user     := 'SOURCE_USER'; 
exec :tbs_dest := 'DEST_TBS';

select 'Transporting tablespace ' || :tbs_source || ' or user ' || :schema_user || ' to tablespace ' || :tbs_dest from dual;

select 'Size: ' || to_char((sum(ext.bytes) / 1048576), '9,990.00') || ' MB'
from dba_objects ob
inner join (
  select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, 'TABLE' as tipo from dba_tables ta
  union
  select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, 'INDEX' as tipo from dba_indexes ind
) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
inner join dba_extents ext on ext.segment_name = ob.object_name
where ob.object_type in ('INDEX', 'TABLE', 'LOB') and (tb.tbs = :tbs_source or ob.owner = :schema_user);

select 'alter user ' || ob.owner || ' quota unlimited on ' || :tbs_dest || ' default tablespace ' || :tbs_dest || ';'
from dba_objects ob
inner join (
  select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, 'TABLE' as tipo from dba_tables ta
  union
  select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, 'INDEX' as tipo from dba_indexes ind
) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
where ob.object_type in ('INDEX', 'TABLE', 'LOB') and (tb.tbs = :tbs_source or ob.owner = :schema_user)
group by ob.owner;

select 
  decode(ob.object_type,
    'TABLE',
      'alter table "' || ob.owner || '"."' || ob.object_name || '" move tablespace ' || :tbs_dest || ';',
      'alter index "' || ob.owner || '"."' || ob.object_name || '" rebuild tablespace ' || :tbs_dest || ';'
  )
from dba_objects ob 
inner join (
  select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, 'TABLE' as tipo from dba_tables ta
  union
  select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, 'INDEX' as tipo from dba_indexes ind
) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
where ob.object_type in ('TABLE') and (tb.tbs = :tbs_source or ob.owner = :schema_user);

select
  'alter table "' || lo.owner || '"."' || lo.table_name ||
  '" move lob ("' || lo.column_name || '") store as (tablespace ' || :tbs_dest || ');'
from dba_lobs lo
inner join  dba_segments se on se.segment_name = lo.segment_name
where se.tablespace_name = :tbs_source or se.owner = :schema_user;

select 
  decode(ob.object_type,
    'TABLE',
      'alter table "' || ob.owner || '"."' || ob.object_name || '" move tablespace ' || :tbs_dest || ';',
      'alter index "' || ob.owner || '"."' || ob.object_name || '" rebuild tablespace ' || :tbs_dest || ';'
  )
from dba_objects ob 
inner join (
  select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, 'TABLE' as tipo from dba_tables ta
  union
  select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, 'INDEX' as tipo from dba_indexes ind
) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
where ob.object_type in ('INDEX') and (tb.tbs = :tbs_source or ob.owner = :schema_user);

You must only change SOURCE_TBS and/or SOURCE_USER for your source tablespace/user and left as '' if it isn't relevant, and specify DEST_TBS.

Bye. Andy.