Show the HWM for a Datafile and Whether they can be Resized 
--
-- Displays Space Usage for Each Datafile and whether it can be Resized
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN file_name FORMAT A80
 
SELECT file_name, hwm, blocks total_blocks, blocks-hwm+1 as Shrinkage_Available
FROM dba_data_files a,
     ( select file_id, max(block_id+blocks) hwm
       from dba_extents
       group by file_id ) b
WHERE a.file_id = b.file_id
/

Related Scripts

blocksdba_data_filesdba_extentsfile_namehwmshrink
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License