oracle-wiki.net - List Fragmentation in a Segment

The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

Back

List Fragmentation in a Segment

Introduction

The following query gives and indication of how much fragmentation is in a segment.

Blocks of type fs1, fs2 and fs3 blocks indicates some fragmentation. Specifically fs1 and fs2 as these blocks may not be able to accommodate inserts. dEven though they contain free space, it is likely the segment will need to extend on insert.

If there are lots of blocks of type fs3 and fs4, but specifically fs4, then lots of blocks will be freed when re-organizing. The re-organizing of the table will increase the number of full blocks and remove empty space.

And example of the output from the query is list below.

-- List Fragmentation in a Segment.

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

SET VERIFY OFF

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 ('&schema_owner', '@table_name', '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('FS1 Blocks = '||v_fs1_blocks);

dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

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

end;

Example Output

Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 0
Full Blocks = 4349057184

PL/SQL procedure successfully completed.

Key
fs1_blocks : Number of blocks having at least 0 to 25% free space
fs2_blocks : Number of blocks having at least 25 to 50% free space
fs3_blocks : Number of blocks having at least 50 to 75% free space
fs4_blocks : Number of blocks having at least 75 to 100% free space
ful1_blocks : Total number of blocks full in the segment

Published on

Published 18th June 2025

Page updated

Google Sites

Report abuse