-- ts_map.sql
-- by Brian Peasland
-- 06 March 2000
--
-- This script shows a "map" of database block allocations
-- for a tablespace. This script can be used to examine
-- free space fragmentation among other issues.
--

SET ECHO OFF
SET VERIFY OFF
SET PAGESIZE 40

SELECT tablespace_name FROM dba_tablespaces;

ACCEPT ts PROMPT 'Enter tablespace name: '

COLUMN tablespace FORMAT a15 
COLUMN file_id    FORMAT 990
COLUMN block_id   FORMAT 9,999,990
COLUMN blocks     FORMAT 999,990
COLUMN segment    FORMAT a38


SELECT tablespace_name as tablespace,
       file_id,
       block_id,
       blocks,
       owner||'.'||segment_name as segment
FROM dba_extents
WHERE tablespace_name = UPPER('&ts')
UNION
SELECT tablespace_name as tablespace,
       file_id,
       block_id,
       blocks,
       '<free>' as segment
FROM dba_free_space
WHERE tablespace_name = UPPER('&ts');