Steps to shrink a large size tablespace in Oracle

Leave a reply

Steps to reclaim the space shrink from large size tablespace in Oracle

For shrink the current tablespace space and reclaim the space from the tablespace, we first move the objects from the tablespace to another tablespace and move back the objects to original tablespace for reclaim the space to the Operating system in Oracle.

Steps to reclaim the space from large size tablespace

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

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

2) Login with the database owner username/password

2.1) Migrate the tables to another tablespace. Generate the table migration script

spool C:\script\moveTables.sql
select '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;

Leave a Reply