Oracle Shrink Datafile

--create tablespace CREATE BIGFILE TABLESPACE "TBS_ADTDATA1" DATAFILE '+DG_DATA/tbs_adtdata1_n.dbf' SIZE 2621440m  AUTOEXTEND ON NEXT 32212254720  MAXSIZE 134217727M   LOGGING ONLINE PERMANENT BLOCKSIZE 32768   EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT   NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;    CREATE BIGFILE TABLESPACE "TBS_ADTDATA1" DATAFILE    '+DG_DATA/tbs_adtdata_n.dbf' SIZE 1677721m   AUTOEXTEND ON NEXT 32212254720 MAXSIZE 134217727M   LOGGING ONLINE PERMANENT BLOCKSIZE 32768   EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT   NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO; --1.enable row movement,data tablespace define OLD_TS_NAME='TS_BLOCK' set serveroutput on; declare    cursor c is select distinct se.owner,se.segment_name from dba_segments se where se.tablespace_name='&OLD_TS_NAME' and se.segment_type in('TABLE','TABLE PARTITION') order by se.owner; begin   for v in c loop     execute immediate 'alter table  '||v.owner||'.'||v.segment_name ||'  enable row movement';     dbms_output.put_line('-->Enable row movement OK :       '||v.owner||'.'||v.segment_name);   end loop; end; / --2.move table define OLD_TS_NAME='TS_BLOCK' define NEW_TS_NAME='USERS' set serveroutput on; declare    cursor c is select se.owner,se.segment_name from dba_segments se where se.tablespace_name='&OLD_TS_NAME' and se.segment_type='TABLE';   sql_stmt varchar2(200);   sql_enable varchar2(200);   sql_disable varchar2(200); begin   execute immediate 'alter session enable parallel ddl';   for v in c loop     sql_stmt:= 'alter table  '||v.owner||'.'||v.segment_name ||'  move tablespace  '|| '&NEW_TS_NAME'||'  parallel 16';     --excute sqls     execute immediate sql_stmt;     dbms_output.put_line('-->Move table OK :             '|| v.segment_name);   end loop; end; /        --3.move table partitions define OLD_TS_NAME='TS_BLOCK' define NEW_TS_NAME='USERS' set serveroutput on; declare    cursor c is select se.owner,se.segment_name,se.partition_name from dba_segments se where se.tablespace_name='&OLD_TS_NAME' and se.segment_type='TABLE PARTITION' order by se.owner;   sql_stmt varchar2(200); begin   execute immediate 'alter session enable parallel ddl';   for v in c loop     --move table partition     sql_stmt:= 'alter table  '||v.owner||'.'||v.segment_name ||'  move partition  '||v.partition_name || ' tablespace   &NEW_TS_NAME  parallel 16';     --dbms_output.put_line(sql_stmt);     --excute sqls     execute immediate sql_stmt;     dbms_output.put_line('-->Move table partition OK:    '|| v.owner||'.'||v.partition_name);   end loop; end; / --4.disable row movment,data tablespace  define NEW_TS_NAME='USERS' set serveroutput on; declare    cursor c is select distinct se.owner,se.segment_name from dba_segments se where se.tablespace_name='&NEW_TS_NAME' and se.segment_type in('TABLE','TABLE PARTITION') order by se.owner; begin   for v in c loop     execute immediate 'alter table  '||v.owner||'.'||v.segment_name ||'  disable row movement';     dbms_output.put_line('-->Disable row movement OK :       '||v.owner||'.'||v.segment_name);   end loop; end; / --5.check all tables to new tablespace,not in old tablespace,on  table/table partitoon  is OK define TS_BLOCK='TS_BLOCK' select * from dba_segments se where se.tablespace_name='&OLD_TS_NAME'; --6..rebuild indexes define OLD_TS_IDX_NAME='TS_BLOCK' define NEW_TS_IDX_NAME='USERS' set serveroutput on; declare    cursor c is select distinct se.owner,se.segment_name from dba_segments se where se.tablespace_name='&OLD_TS_IDX_NAME' and se.segment_type='INDEX' order by se.owner;   sql_stmt varchar2(200); begin   execute immediate 'alter session enable parallel ddl';   for v in c loop     --move table partition     sql_stmt:= 'alter index  '||v.owner||'.'||v.segment_name ||'  rebuild tablespace   &NEW_TS_IDX_NAME  parallel 16 nologging';     --dbms_output.put_line(sql_stmt);     --excute sqls      execute immediate sql_stmt;     dbms_output.put_line('-->Rebuild  index  OK:    '|| v.owner||'.'||v.segment_name);   end loop; end; / --7.rebuild partiton indexes define OLD_TS_IDX_NAME='TS_BLOCK' define NEW_TS_IDX_NAME='USERS' set serveroutput on; declare    cursor c is select distinct se.owner,se.segment_name,se.partition_name from dba_segments se where se.tablespace_name='&OLD_TS_IDX_NAME' and se.segment_type='INDEX PARTITION' order by se.owner;   sql_stmt varchar2(200); begin   execute immediate 'alter session enable parallel ddl';   for v in c loop     --move table partition     sql_stmt:= 'alter index  '||v.owner||'.'||v.segment_name ||'  rebuild partition  '||v.partition_name||'  tablespace   &NEW_TS_IDX_NAME  parallel 16 nologging';     --dbms_output.put_line(sql_stmt);     --excute sqls     execute immediate sql_stmt;     dbms_output.put_line('-->Rebuild  index  OK:    '||v.owner||'.'|| v.segment_name);   end loop; end; / --8.check all indexes to new tablespace ,not in old tablespace ,no index index partition   is OK define OLD_TS_IDX_NAME='TS_BLOCK' select * from dba_segments se where se.tablespace_name='&OLD_TS_IDX_NAME'; --9.gather table/index stats for new tables for update in new tablespace define NEW_TS_NAME='USERS' set serveroutput on; declare   cursor c is select distinct se.owner,se.segment_name from dba_segments se where se.tablespace_name='&NEW_TS_NAME' and se.segment_type in('TABLE','TABLE PARTITION'); begin   for v in c loop     if c%NOTFOUND then       dbms_output.put_line('No data found!');     else       --dbms_output.put_line(v.owner||v.segment_name);       dbms_stats.gather_table_stats(ownname => v.owner,tabname => v.segment_name,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE 1',degree => dbms_stats.AUTO_DEGREE,cascade => true);       dbms_output.put_line('Gather stats OK : ' || v.owner||'.'||v.segment_name);     end if;   end loop; end; / --10.check statst on all objects,no data os OK define NEW_TS_NAME='TS_BLOCK'; define NEW_TS_IDX_NAME='TS_BLOCK' select se.owner,se.TABLE_NAME,se.ROW_MOVEMENT,se.degree,se.LAST_ANALYZED from dba_tables se where se.TABLESPACE_NAME='NEW_TS_NAME' and se.LAST_ANALYZED is null; select es.owner,es.index_name,es.degree,es.LAST_ANALYZED from dba_indexes es where es.TABLESPACE_NAME='NEW_TS_NAME' and es.LAST_ANALYZED is null; --11.drop old tablespaces; drop tablespace xx_idx including contents and datafiles; drop tablespace xx_data including contents and datafile;