How to identify the objects consuming more space in Oracle database

Introduction:

In this blog, will see how to identify the objects which are consuming more space in an Oracle database and options to reclaim the space.

Description:

In an OLTP database environments, the objects can grow large and sometimes the fragmentation can happen due to DML operations.

Performance of the queries which are running against such objects can be impacted, so it’s quite important to reclaim the fragmented space and identify the objects which are growing large in the database.

Following examples are from an EBS database on version 12c.

Query to find the large objects in the database:

select * from

(select owner,segment_name||’~’||partition_name segment_name,segment_type,bytes/(1024*1024) size_m, tablespace_name

from dba_segments

ORDER BY BLOCKS desc) where rownum < 4;

OWNER                SEGMENT_NAME                         SEGMENT_TYPE           SIZE_M TABLESPACE_NAME

——————– ———————————— —————— ———- —————

APPLSYS              WF_ITEM_ACTIVITY_STATUSES_H~         TABLE              401161.875 APPS_TS_TX_DATA

APPLSYS              SYS_LOB0000057442C00004$$~           LOBSEGMENT           315701.5 APPS_TS_MEDIA

APPLSYS              WF_ITEM_ACTIVITY_STATUSES_H_N1~      INDEX               261410.25 APPS_TS_TX_IDX

 

To find the tables with lob segments from the above large object:

select owner,segment_name,table_name,column_name,tablespace_name

from dba_lobs

where segment_name in (‘SYS_LOB0000057442C00004$$’);

 

OWNER       SEGMENT_NAME                     TABLE_NAME      COLUMN_NAME          TABLESPACE_NAME

———– ——————————– ————— ——————– —————

APPLSYS     SYS_LOB0000057442C00004$$        FND_LOBS        FILE_DATA            APPS_TS_MEDIA

 

To find size of the table containing lob segments:

set lines 200

col column_name format a30

SELECT owner,table_name, column_name, segment_name, a.bytes/1024/1024 SIZE_MB

FROM dba_segments a JOIN dba_lobs b

USING (owner, segment_name)

WHERE b.table_name in (‘FND_LOBS’);

 

OWNER                TABLE_NAME   COLUMN_NAME       SEGMENT_NAME                    SIZE_MB

——————– ———— —————– —————————- ———-

APPLSYS              FND_LOBS     FILE_DATA         SYS_LOB0000057442C00004$$    315843.375

 

To find the data blocks usage for the large table:

set serveroutput on

declare

v_unformatted_blocks number;

v_unformatted_bytes number;

v_fs1_blocks number;

v_fs1_bytes number;

v_fs2_blocks number;

v_fs2_bytes number;

v_fs3_blocks number;

v_fs3_bytes number;

v_fs4_blocks number;

v_fs4_bytes number;

v_full_blocks number;

v_full_bytes number;

begin

dbms_space.space_usage (

‘APPLSYS’,

‘WF_ITEM_ACTIVITY_STATUSES_H’,

‘TABLE’,

v_unformatted_blocks,

v_unformatted_bytes,

v_fs1_blocks,

v_fs1_bytes,

v_fs2_blocks,

v_fs2_bytes,

v_fs3_blocks,

v_fs3_bytes,

v_fs4_blocks,

v_fs4_bytes,

v_full_blocks,

v_full_bytes);

dbms_output.put_line(‘Unformatted Blocks = ‘||v_unformatted_blocks);

dbms_output.put_line(‘Blocks with 00-25% free space = ‘||v_fs1_blocks);

dbms_output.put_line(‘Blocks with 26-50% free space = ‘||v_fs2_blocks);

dbms_output.put_line(‘Blocks with 51-75% free space = ‘||v_fs3_blocks);

dbms_output.put_line(‘Blocks with 76-100% free space = ‘||v_fs4_blocks);

dbms_output.put_line(‘Full Blocks = ‘||v_full_blocks);

end;

/

Unformatted Blocks = 0

Blocks with 00-25% free space = 0

Blocks with 26-50% free space = 0

Blocks with 51-75% free space = 2

Blocks with 76-100% free space = 8

Full Blocks = 49320007

PL/SQL procedure successfully completed.

Options to reclaim space from the fragmented and large objects:

1. 1. Delete obsolete data for custom tables.
   2. Run purge program to clear old data for seeded tables.
   3. Export/Import data on a table.
   4. Move table to a different tablespace and rebuild indexes, gather tables stats.
   5. Shrink space

Bottom line: 

Database periodic maintenance like reorg will keep the database growth under control. Oracle provides various options to perform, once we identify the objects occupying more space in the database.