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;