Best Script in the World: HWM.SQL

This is a script with a very long story. But to cut it short let's imagine you just have rebuilt an entire tablespace flooded with fragmentation and now extents are well packed inside datafiles and you want to size down the most space you can down to the OS. How can you find the High Water Mark (HWM) from all those files?

Easy, just use the following SQL*Plus script (don't use it against a 10.2.0.1 database!!):

column file_name format a50; 
column tablespace_name format a15; 
column Bytes format 9999999999; 
column KBytes format 9999999999;
column KBytes format 9999999999;
set pagesize 9999 
set verify off

select --+ LMC
a.tablespace_name 
       ,a.file_name 
       ,trunc((b.maximum+c.blocks-1)*d.db_block_size/1024/1024)+1 Megabytes
       ,a.bytes/1024/1024 datafilesize_mb
from   dba_data_files a 
       ,(select file_id,max(block_id) maximum 
         from dba_extents 
         group by file_id) b 
       ,dba_extents c 
       ,(select value db_block_size 
         from v$parameter 
         where name='db_block_size') d 
where a.file_id  = b.file_id 
and   c.file_id  = b.file_id 
and   c.block_id = b.maximum 
and   a.tablespace_name like '&Nome_Tablespace'
order by a.tablespace_name,a.file_name 
/

This will give you the exact size you should downsize your datafiles to, and then it's up to you to cut them near the edge or not.

This script is actually very good also to simulate load when you use the SYSTEM tablespace as parameter. Not that you'll start trimming down the SYSTEM tablespace data files, that's not the point. Every once in a while you'll have the need to simulate workload in your database, specially stressing out the data dictionary, so this script could be a good option to accomplish that.