-- hwm_df.sql
-- jkstill@gmail.com
-- 11/06/2008 - complete rewrite
-- for tablespaces that are completely devoid of objects
-- (aka empty) this report will assume 128k is the minimum file size
-- this may or not be correct for your implementation
-- and probably is not terribly important to worry about

@clears

@title 'Datafile High Water Marks' 150

col file_name format a50 head 'FILE NAME'
col file_size format 999,999,999,999 head 'FILE SIZE|BYTES'
col shrink_size format 999,999,999,999 head 'SHRINK TO|SIZE BYTES'
col tablespace_name format a15 head 'TABLESPACE'
col bytes_saved format 999,999,999,999 head 'BYTES SAVED'
col max_block_id format 999999999 head 'MAX BLOCK ID'

break on tablespace_name skip 1
break on report
compute sum of bytes_saved on tablespace_name
compute sum of bytes_saved on report
compute sum of file_size on tablespace_name
compute sum of file_size on report
compute sum of shrink_size on tablespace_name
compute sum of shrink_size on report

with maxext as (
	select file_id, max(block_id) max_block_id
	from dba_free_space
	group by file_id
),
shrink as (
	select
	t.tablespace_name
	, f.file_id
	, f.file_name
	, m.max_block_id
	, f.blocks * t.block_size file_size
	, case 
		when ((nvl(m.max_block_id,f.blocks+1)-1) * t.block_size) >= (128*1024)
			then(nvl(m.max_block_id,f.blocks+1)-1) * t.block_size
		else
			128*1024
	end shrink_size
from maxext m
	, dba_data_files f
	, dba_tablespaces t
where m.file_id(+) = f.file_id
and t.tablespace_name = f.tablespace_name
and t.contents = 'PERMANENT'
)
select 
	s.tablespace_name
	, s.file_id
	, s.max_block_id
	, s.file_name
	, s.file_size
	, s.shrink_size
	, (s.file_size - s.shrink_size) bytes_saved
from shrink s
order by tablespace_name, file_id
/