set serveroutput on
define tsname='USERS'
define totsname ='TS_EXCHANGE'
declare
strtabsql varchar2(200);
stridxsql varchar2(200);
strlobsegsql varchar2(200);
strlobidxsql varchar2(200);
begin
for c_tabresult in (select owner,segment_name,bytes/1024/1024,segment_type
from dba_segments
where tablespace_name='&tsname'
and segment_type ='TABLE'
order by 3 desc) loop
strtabsql:= 'ALTER table ' || c_tabresult.owner || '.' || c_tabresult.segment_name || ' move tablespace &totsname';
dbms_output.put_line(strtabsql);
execute immediate strtabsql;
for c_idxresult in (
select owner,index_name
from dba_indexes
where tablespace_name='&tsname'
and table_name =c_tabresult.segment_name) loop
stridxsql:='alter index ' || c_idxresult.owner || '.' || c_idxresult.index_name || ' rebuild online tablespace &totsname';
dbms_output.put_line(stridxsql);
execute immediate stridxsql;
end loop;
for c_lobsegresult in (
select a.owner,b.table_name,b.column_name,b.SEGMENT_NAME
from dba_segments a,dba_lobs b
where a.segment_name=b.segment_name
and a.tablespace_name='&tsname'
and b.table_name=c_tabresult.segment_name
and a.segment_type='LOBSEGMENT') loop
strlobsegsql:='ALTER TABLE ' || c_lobsegresult.owner || '.' || c_lobsegresult.table_name || ' move tablespace &totsname lob(' || c_lobsegresult.column_name || ') store as ' || c_lobsegresult.SEGMENT_NAME || ' (TABLESPACE &totsname)';
dbms_output.put_line(strlobsegsql);
execute immediate strlobsegsql;
END loop;
for c_lobidxresult in (
select a.owner,b.table_name,b.column_name,b.SEGMENT_NAME
from dba_segments a,dba_lobs b
where a.segment_name=b.segment_name
and a.tablespace_name='&tsname'
and b.table_name=c_tabresult.segment_name
and a.segment_type='LOBINDEX') loop
strlobidxsql:='ALTER TABLE ' || c_lobidxresult.owner || '.' || c_lobidxresult.table_name || ' move tablespace &totsname lob(' || c_lobidxresult.column_name || ') store as ' || c_lobidxresult.SEGMENT_NAME || ' (TABLESPACE &totsname)';
dbms_output.put_line(strlobidxsql);
execute immediate strlobidxsql;
END loop;