Oracle 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.
