Sunday, November 7, 2010

Use SQL to check datafile highwater mark for resizing


select t.file_id,t.file_name,
t.tablespace_name,
round(t.bytes/1024/1024) mbytes,
autoextensible,
round(hw_mark/1024/1024) hw_mark,
round((t.bytes-hw_mark)/1024/1024) resizable,
round((f.free_bytes)/1024/1024) free_mbytes
from dba_data_files t,
(select file_id, max(block_id+blocks)*8192
hw_mark from dba_extents group by file_id) w,
(select file_id,sum(bytes) free_bytes
from dba_free_space group by file_id) f
where t.file_id=w.file_id
and t.file_id=f.file_id(+)