A simple script to list all the used extents and free space in a tablespace, ordered by file id and block id. You have to add your own page, column and break settings. This could be a useful driving query for code to reduce the file sizes of a tablespace after taking some action that you hope will free up some space in the tablespace.
See the pingback in comment #1 for an article about using this script. Note that any query against dba_extents is fairly expensive when the tablespace is locally managed because of the implementation of the x$ktfbue stucture that represents the used extents: you will see a TT lock, a recursive query against seg$ and (possibly) a physical disk read (of the segment header block) for each segment in the tablespace.
rem rem Script: ts_hwm.sql rem Author: Jonathan Lewis rem Dated: Feb 2010 rem Purpose: rem rem Last tested rem 18.104.22.168 rem 22.214.171.124 rem 10.2.0.3 rem Not tested rem 10.2.0.5 rem 126.96.36.199 rem 188.8.131.52 rem Not relevant rem rem Notes: rem Quick and dirty to list extents in a tablespace rem in file and block order. rem rem For LMTs, expect to acquire one TT lock per segment rem in the tablespace, and to query seg$ once for each rem segment in the tablespace. This is a side effect of rem the mechanism invoked by accessing x$ktfbue. Also rem assume that you will do one phsyical block read per rem segment (reading the segment header block for the rem extent map) as this is also part of the implementation rem of x$ktfbue. rem rem Watch out for objects in the recyclebin - they will show rem up as FREE in dba_free_space, but will stop you from rem resizing the tablespace until you purge them. Depending rem on version of Oracle you may get some clues about this rem because each "free" extent in the recyclebin is reported rem as a separate extent by dba_free_space. rem define m_tablespace = 'TEST_8K' select file_id, block_id, block_id + blocks - 1 end_block, owner, segment_name, partition_name, segment_type from dba_extents where tablespace_name = '&m_tablespace' union all select file_id, block_id, block_id + blocks - 1 end_block, 'free' owner, 'free' segment_name, null partition_name, null segment_type from dba_free_space where tablespace_name = '&m_tablespace' order by 1,2 /