• Arun Kumar

Reclaim Unused Space in Oracle

Over a period of time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.

A DBA must reclaim this unused space inside Oracle so that they do not continue to extend datafiles to accommodate new data


Reclaim Space from Table Segment


Run below query to find top 10 largest segments inside a database

COLUMN owner FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
SELECT * FROM (SELECT segment_type, segment_name, owner, tablespace_name, bytes/1024/1024 size_mb 
FROM dba_segments ORDER BY 5 DESC)
WHERE  ROWNUM <= 10;

To reclaim unused space from a table segment, first enable row movement

ALTER TABLE hr.employees ENABLE ROW MOVEMENT;

Recover the unused space and reset the high water mark (HWM)

ALTER TABLE hr.employees SHRINK SPACE;

In case table has dependent objects then use CASCADE

ALTER TABLE hr.employees SHRINK SPACE CASCADE;


Reclaim Space from Index Segment


First analyze if an index segment requires rebuild because its resource intensive process

ANALYZE INDEX idx_empid VALIDATE STRUCTURE;

SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;
   
  NAME          HEIGHT      LF_ROWS    LF_BLKS    DEL_LF_ROW
  ------------- ----------- ---------- ---------- ----------
  IDX_EMPID               2          1          3          6 
   
  1 row selected.

Rebuild index only if you see HEIGHT is above 4 and Deleted Leaf Row is less than 20. You can simply rebuild an index to reclaim space

ALTER INDEX idx_empid REBUILD ONLINE;


Reclaim Space from LOB Segment


Reclaiming space for an LOB segment is separate from table segment. You must shrink LOB segment separately

ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);


Reclaim Space from Datafile


We will first run Possible Saving Report that will list out the data files along with possible savings in MBs

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Possible Size(MB)"
column currsize format 999,990 heading "Current|Size(MB)"
column savings  format 999,990 heading "Possible|Savings(MB)"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/

Alter Tablespace Coalesce: Even though SMON performs tablespace coalesce time to time, still our first step is to try it out and see if it works for us

SQL> alter tablespace users coalesce;

Now try to shrink the datafile

SQL> ALTER DATABASE DATAFILE 72 RESIZE 1G;
ALTER DATABASE DATAFILE 72 RESIZE 1G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value 

Purge Tablespace: Objects belonging to a tablespace might reside under Recyclebin which does not allow you to shrink the datafile. We must remove the tablespace specific segments from recycle bin first

SQL> purge tablespace users;

Lets try to shrink the datafile

SQL> ALTER DATABASE DATAFILE 72 RESIZE 1G;
ALTER DATABASE DATAFILE 72 RESIZE 1G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value 

Purge Recyclebin: The PURGE TABLESPACE command only removes recyclebin segments belonging to the currently connected user. There might be other users who have deleted objects from the tablespace that reside in recyclebin. Its a good idea to purge recyclebin as sysdba

SQL purge recyclebin;

Let's resize the datafile

SQL> ALTER DATABASE DATAFILE 72 RESIZE 1G;
Database altered.


Reclaim Space from Undo Tablespace


Reclaiming space from UNDO tablespace is very simple. Create a new undo tablespace and drop the old one

CREATE UNDO TABLESPACE undo2 DATAFILE '/u01/orcl/undo02.dbf' SIZE 1G;

ALTER SYSTEM SET UNDO_TABLESPACE=undo2;

DROP TABLESPACE undo1 INCLUDING CONTENTS AND DATAFILES;


Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir