Oracle Tablespace Reorg to reclaim space


Oracle Tablespace Reorg to reclaim space

I have a tablespace, which is having 50 datafiles with the size of 38G, but the actual data in the tablespace is 2G, so I want to reclaim the space from tablespace by reducing the count of datafiles to 3 x8G=24G, so that mount point will release the remaining space.

So, I followed below steps to do the activity:

1. Shutdown Services except DB

2. Spool Segment info
spool on segment_info.txt
select segment_name, segment_type from dba_segments where tablespace_name=’TS_TCS_GP’;
spool off

3. Take segment count
SELECT COUNT(*) FROM DBA_SEGMENTS where tablespace_name=’TS_TCS_GP’ and SEGMENT_TYPE=’TABLE’;
SELECT COUNT(*) FROM DBA_SEGMENTS where tablespace_name=’TS_TCS_GP’ and SEGMENT_TYPE=’INDEX’;

4. Take mount point size before re-org

5. Take estimate of job
(expdp system/manager tablespaces=TS_TCS_GP ESTIMATE_ONLY=y)

6. Identify mount point where you need to take export and check whether you have sufficient space or not in the mount point.

7. Start exporting
(expdp system/manager tablespaces=TS_TCS_GP directory=DATA_PUMP_DIR dumpfile=TS_TCS_GP.dmp logfile=TS_TCS_GP.log)

8. Drop Materialized views in that particular tablespace
(select ‘drop materialized view ‘||owner||’.’||name||’ PRESERVE TABLE;’ from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name = ‘TS_TCS_GP’);)
(select object_name from dba_objects where owner=’SCHEMA NAME” and object_type=’MATERIALIZED VIEW’;)
(Example Drop Command “drop materialized view SCHEMANAME.MATERIALIZED VIEWNAME TABLE”)

9. Query for LOB Objects (select owner,table_name,tablespace_name from dba_lobs where tablespace_name=’TS_TCS_GP’;)

10. Drop LOB Objects (drop table dmtrag.OBJECTNAME CASCADE CONSTRAINTS;)

11. Purge Recyclebin (purge dba_recyclebin;)

12. Check for constraints and disable them (select owner, constraint_name,table_name,index_owner,index_name from dba_constraints where (index_owner,index_name) in (select owner,index_name from dba_indexes where tablespace_name=’TS_TCS_GP’);)
alter table SCHEMANAME.TABLENAME DISABLE constraint CONSTAINTNAME;
alter table SCHEMANAME.TABLENAME DISABLE constraint CONSTAINTNAME;

13. drop tablespace TS_TCS_GP including contents and datafiles CASCADE CONSTRAINTS;

14. Create new tablespace (create tablespace TS_TCS_GP datafile ‘/u02/oradata/cachk/TS_TCS_GP01.dbf’ size 100m autoextend on next 100m maxsize 8g;)

15. Add 2 more datafile (alter tablespace TS_TCS_GP add datafile ‘/u02/oradata/cachk/TS_TCS_GP02.dbf’ size 100m autoextend on next 100m maxsize 8g; and alter tablespace TS_TCS_GP add datafile ‘/u02/oradata/cachk/TS_TCS_GP03.dbf’ size 100m autoextend on next 100m maxsize 8g;)

16. Import (impdp system/manager tablespaces=TS_TCS_GP directory=DATA_PUMP_DIR dumpfile=TS_TCS_GP_Jun14.dmp logfile=TS_TCS_GP_Imp.log)

17. Enable Constraints
alter table SCHEMANAME.TABLENAME  ENABLE constraint CONSTAINTNAME;
alter table SCHEMANAME.TABLENAME  ENABLE constraint CONSTAINTNAME;


Nagulu Polagani

"We are all apprentices in a craft where no one ever becomes a master."

Comments