Oracle Database 11g -- Extent Map for a Datafile

Problem

You want to see the contiguous blocks of used and free space of a datafile, to estimate if it worth to move objects from the end of a datafile to perform datafile shrinking 

Solution

Use the below query to see the contiguous free and used blocks in a datafile:

select group_id block_id, type, sum(blocks) blocks, sum(blocks)*8192 bytes
from (
    select nvl(group_id , lag(group_id ignore nulls) over (order by block_id)) group_id, type, blocks
    from (
        select case when type = prev_type and c1 = 0 then null else block_id end group_id, t.* 
        from (
            select type, block_id, blocks, lag(type, 1, type) over (order by block_id) prev_type, block_id  - lag(next_block_id) over (order by block_id) c1
            from (
                select 'XXXX' type, block_id, blocks, block_id+blocks next_block_id from dba_extents where file_id=<file_id>
                union all
                select 'OOOO', block_id, blocks, block_id+blocks bext_block_id from dba_free_space where file_id=<file_id>
            ) where rownum>0
        ) t where rownum>0
    ) t where rownum>0
) t 
group by group_id, type
order by group_id ;

XXXX - will stand for used blocks

OOOO - will stand for free blocks