Move all database objects from one tablespace to another

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:

1、此脚本只用于产生相应语句。

2、如果是同一用户下的表schema_user 为空，否则会把所有用户下数据查出来

---------------------------------------------------------------------

 

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  := 'TSSAWF';
-- '' if it isn't relevant.
exec :schema_user     := '';
exec :tbs_dest := 'TSSAIOMS';

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