Oracle Tablespace High water mark

Tablespace High water mark
Tablespace HWM



col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999

SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
ANUJTEST        /opt/app/oracle/oradata/orcl/anujtest.dbf                 10      8        2
EXAMPLE         /opt/app/oracle/oradata/orcl/example01.dbf               100     81       19
PERFSTAT        /opt/app/oracle/oradata/orcl/anuj_perfstat.dbf          1000    115      885
RMAN            /opt/app/oracle/oradata/orcl/rman.dbf                     50      7       43
SYSAUX          /opt/app/oracle/oradata/orcl/sysaux01.dbf                830    777       53
SYSTEM          /opt/app/oracle/oradata/orcl/system01.dbf                840    823       17
TSAPEXU         /opt/app/oracle/oradata/orcl/tsapexu01.dbf               110     97       13
UNDOTBR         /opt/app/oracle/oradata/orcl/undotbR.dbf                 500     46      454
USERS           /opt/app/oracle/oradata/orcl/users01.dbf                 609    576       33

9 rows selected.