Move segments from one Tablespace to another

Move Tables of user PROD_USER like this:
Tables + indexes of tables EMP,PRODUCTS,CUSTOMERS into tablespace TBS1.
All the other tables + indexes of this user into tablespace TBS2.



set serveroutput on

--***********************************************
-- (Run the script as DBA user)
-- Parameters:
---------------
-- user_name : owner to which to move segments
-- TBS1 : Tablespace-A
-- Tables_TBS1 : list of tables to move to tablespace-A
-- TBS2 : tablespace to move all tables NOT in the list
-- put_Output : if 'true' - create output of operations (dbms_output)
-- put_Execute : if 'true' - execute the move operations
--***********************************************
declare
User_Name varchar2(20) default 'PROD_USER';
TBS1 varchar2(20) default 'TBS1';
Tables_TBS1 varchar2(1000) default 'EMP,PRODUCTS,CUSTOMERS';
TBS2 varchar2(20) default 'TBS2';
put_Output boolean default true;
put_Execute boolean default true;
Sort_memory number default 10000000;
TBS varchar2(20);
begin
Tables_TBS1 := upper(','||Tables_TBS1||',');
execute immediate 'alter session set sort_area_size = '||to_char(Sort_memory);
for crs in (select distinct s.owner, s.segment_name, s.partition_name, s.tablespace_name, s.segment_type from dba_segments s where owner like User_Name and segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')) loop
if instr(Tables_TBS1,','||crs.segment_name||',') != 0 then
TBS := TBS1;
else
TBS := TBS2;
end if;
if crs.tablespace_name = TBS then
--------------------------------------------------
-- Table is already in the correct tablespace.
-- check only indexes.
--------------------------------------------------
for crs2 in (select distinct s.owner, s.segment_name, s.partition_name, s.tablespace_name, s.segment_type from dba_indexes i, dba_segments s
where i.table_owner=crs.owner and i.table_name = crs.segment_name and s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
and s.owner = i.owner and s.segment_name = i.index_name and (s.partition_name = crs.partition_name or s.partition_name is null and crs.partition_name is null)) loop
if instr(Tables_TBS1,','||crs.segment_name||',') != 0 then
TBS := TBS1;
else
TBS := TBS2;
end if;
if crs2.tablespace_name != TBS then
if crs2.segment_type in ('INDEX PARTITION') then
if put_Output then dbms_output.put_line ('> INDEX PARTITION '||crs2.owner||'.'||crs2.segment_name||':'||crs2.partition_name||' -> '||TBS); end if;
if put_Execute then execute immediate 'alter index '||crs2.owner||'.'||crs2.segment_name||' rebuild partition '||crs2.partition_name ||' tablespace '||TBS; end if;
elsif crs2.segment_type in ('INDEX SUBPARTITION') then
if put_Output then dbms_output.put_line ('> INDEX SUBPARTITION '||crs2.owner||'.'||crs2.segment_name||':'||crs2.partition_name||' -> '||TBS); end if;
if put_Execute then execute immediate 'alter index '||crs2.owner||'.'||crs2.segment_name||' rebuild subpartition '||crs2.partition_name ||' tablespace '||TBS; end if;
elsif crs2.segment_type = 'INDEX' then
if put_Output then dbms_output.put_line ('> INDEX '||crs2.owner||'.'||crs2.segment_name||' -> '||TBS); end if;
if put_Execute then execute immediate 'alter index '||crs2.owner||'.'||crs2.segment_name||' rebuild tablespace '||TBS; end if;
end if;
end if;
end loop;
else
--------------------------------------------------
-- Move Table AND all rebuild ALL the indexes.
--------------------------------------------------
if crs.segment_type in ('TABLE PARTITION') then
if put_Output then dbms_output.put_line ('TABLE PARTITION '||crs.owner||'.'||crs.segment_name||':'||crs.partition_name||' -> '||TBS); end if;
if put_Execute then execute immediate 'alter table '||crs.owner||'.'||crs.segment_name||' move partition '||crs.partition_name ||' tablespace '||TBS; end if;
elsif crs.segment_type in ('TABLE SUBPARTITION') then
if put_Output then dbms_output.put_line ('TABLE SUBPARTITION '||crs.owner||'.'||crs.segment_name||':'||crs.partition_name||' -> '||TBS); end if;
if put_Execute then execute immediate 'alter table '||crs.owner||'.'||crs.segment_name||' move subpartition '||crs.partition_name ||' tablespace '||TBS; end if;
elsif crs.segment_type = 'TABLE' then
if put_Output then dbms_output.put_line ('TABLE '||crs.owner||'.'||crs.segment_name||' -> '||TBS); end if;
if put_Execute then execute immediate 'alter table '||crs.owner||'.'||crs.segment_name||' move tablespace '||TBS; end if;
end if;
for crs2 in (select distinct s.owner, s.segment_name, s.partition_name, s.tablespace_name, s.segment_type from dba_indexes i, dba_segments s
where i.table_owner=crs.owner and i.table_name = crs.segment_name and s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
and s.owner = i.owner and s.segment_name = i.index_name and (s.partition_name = crs.partition_name or s.partition_name is null and crs.partition_name is null)) loop
if crs2.segment_type in ('INDEX PARTITION') then
if put_Output then dbms_output.put_line ('> INDEX PARTITION '||crs2.owner||'.'||crs2.segment_name||':'||crs2.partition_name||' -> '||TBS); end if;
if put_Execute then execute immediate 'alter index '||crs2.owner||'.'||crs2.segment_name||' rebuild partition '||crs2.partition_name ||' tablespace '||TBS; end if;
elsif crs2.segment_type in ('INDEX SUBPARTITION') then
if put_Output then dbms_output.put_line ('> INDEX SUBPARTITION '||crs2.owner||'.'||crs2.segment_name||':'||crs2.partition_name||' -> '||TBS); end if;
if put_Execute then execute immediate 'alter index '||crs2.owner||'.'||crs2.segment_name||' rebuild subpartition '||crs2.partition_name ||' tablespace '||TBS; end if;
elsif crs2.segment_type = 'INDEX' then
if put_Output then dbms_output.put_line ('> INDEX '||crs2.owner||'.'||crs2.segment_name||' -> '||TBS); end if;
if put_Execute then execute immediate 'alter index '||crs2.owner||'.'||crs2.segment_name||' rebuild tablespace '||TBS; end if;
end if;
end loop;
end if;
end loop;
end;
/