Move all database objects from one tablespace to another
Submitted by lodopidolo on Wed, 2010-05-26 13:50
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.
»
- lodopidolo's blog
- Log in to post comments