Steps to Shrink a Big Tablespace 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/LOBSegments 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 tablespace
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 tablespace offline or Drop if no object present in it.
alter tablespace APP_TB offline;
