Tablespace HWM

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 structure 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             12.1.0.2
rem             11.2.0.3
rem             11.1.0.7
rem             10.2.0.3
rem     Not tested
rem             10.2.0.5
rem              9.2.0.8
rem              8.1.7.4
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 physical 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. Note that dba_extents
rem     and dba_segments behave differently, the latter reports
rem     objects in the recyclebin, the former doesn't so sum(extents)
rem     doesn't match sum(segments)
rem

start setenv

define m_tablespace = 'TEST_8K'

break on file_id skip 1

column  file_id         format 99,999
column  block_id        format 99,999,999
column  end_block       format 99,999,999
column  owner           format a10
column  partition_name  format a25      noprint
column  segment_name    format a28

spool ts_hwm

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 = upper('&m_tablespace')
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 = upper('&m_tablespace')
order by 
        1,2
/

spool off

set doc off
doc

Sample of output when there is an item in the recyclebin (blocks 640 - 767)
---------------------------------------------------------------------------
FILE_ID    BLOCK_ID   END_BLOCK OWNER      SEGMENT_NAME    SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- ------------------
      5         128         255 free       free
                256         383 TEST_USER  T2              TABLE
                384         511 free       free
                512         639 TEST_USER  T2_I1           INDEX
                640         767 free       free
                768      63,999 free       free


#