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:
-
- Delete obsolete data for custom tables.
- Run purge program to clear old data for seeded tables.
- Export/Import data on a table.
- Move table to a different tablespace and rebuild indexes, gather tables stats.
- 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.
