SQL Script Tablespace Mapper

Updated : 4-August-1998
Version : 1.0

Description

This script lists all used and free extents of a given tablespace. This is an easy way to see tablespace fragmentation.

Parameters

$tspace - The Tablespace to Map..

SQL Source

set pages 60
set lines 132
set ver off

col owner form a10
col file_id form 9999 heading "File ID"
col block_id form 9999999 heading "Block ID"

ACCEPT tspace  prompt 'Enter Tablespace Name : '

spool mapper.lst

select 'FREE SPACE' owner
       , ' ' object
       , file_id
       , block_id
       , bytes
from dba_free_space
where tablespace_name = UPPER('&tspace')
union
select SUBSTR(owner,1,20)
       , SUBSTR(segment_name,1,32)
       , file_id
       , block_id
       , bytes
from dba_extents
where tablespace_name = UPPER('&tspace')
order by 3,4
/
spool off

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal