Steps to Shrink a Big Table-space Oracle

1) Login with DBA account and create new tablespaces for the database user.

create tablespace APP_TB2 datafile  'c:\oradata\apps0.dbf' ze 256m autoextend on next 128m maxsize unlimited;

2) Login with the db owner username/password

2.1) Migrate the tables
Generate the table migration script
 
spool C:\script\moveTables.sqlselect 'alter table ' || SEGMENT_NAME || ' move tablespace APP_TB2;'
FROM dba_Segments a, dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name='APP_TB' and segment_type='TABLE'
order by FILE_NAME,segment_name;
spool off;

2.2) Migrate the Indexes
 
Generate the index migration script

spool C:\script\MoveIndex.sql
select 'alter index ' || SEGMENT_NAME || ' rebuild tablespace APP_TB2;'
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name='APP_TB' and segment_type='INDEX'
order by FILE_NAME,segment_name;
spool off;

2.3) Migrate the LOB/Lob Segments if possible

spool /tmp/username/MoveLob.sql
select 'ALTER TABLE ' || table_name || ' move lob(' || COLUMN_NAME || ') STORE AS (TABLESPACE APP_TB2);'
from dba_tab_columns
where owner='APP_TB' and data_type='CLOB';
spool off;

3) Check if anything missing in the original table space

set line 300
col owner format A26
col segment_name format A26
col segment_type format A26
col tablespace_name format A26
col relative_fno format 99999
col file_name format A50
SELECT owner, segment_name, segment_type,a.tablespace_name, a.relative_fno, b.file_name
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name='TB_APP'
order by FILE_NAME,segment_name;


4) Never forget to change the default tablespace of the user to the new one

ALTER USER default tablespace TB_APP2;


5) Change table-space offline or Drop if no object present in it.

alter tablespace APP_TB offline;