Category: Oracle

Sometimes, in order to see fragmented tables in Oracle database, you need to check the block map in datafiles.

Here, I’m sharing with you a script for checking that 🙂

col segment_name format a30
col owner format a30
col segment_type format a30
col partition_name format a45
col segment_type format a30
select  file_id,
        block_id,
        block_id + blocks - 1   end_block,
        blocks,
        owner,
        segment_name,
        partition_name,
        segment_type
from dba_extents
where
        tablespace_name = 'SYSAUX'
union all
select  file_id,
        block_id,
        block_id + blocks - 1   end_block,
        blocks,
        'free'                  owner,
        'free'                  segment_name,
        null                    partition_name,
        null                    segment_type
from dba_free_space 
where tablespace_name = 'SYSAUX'
order by 1,2;