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

• Segment Space Usage By Owner
• Shows Temporary Tablespace Usage
• Tablespace Space Usage
• View Datafile Info
• Show the High Water Mark for a Given Table
• List the Number of Extents for All Indexes for a Given Table
• List Tables with More than One Extent
• Show Gaps in Tablespace or Specific Datafile
• Displays Temp Space Currently in use by Users
• Displays Space Usage for Each Datafile