SELECT file_name, CEIL (blocks * t.block_size / 1024 / 1024) current_mb,
CEIL ((NVL (hwm, 1) * t.block_size) / 1024 / 1024) smallest_mb
FROM dba_data_files a,
dba_tablespaces t,
(SELECT file_id, MAX (block_id + blocks - 1) hwm
FROM dba_extents
GROUP BY file_id) b
WHERE t.tablespace_name = a.tablespace_name
AND a.file_id = b.file_id(+);

ALTER DATABASE DATAFILE '/app/oracle/db/product/11.2.0/dbhome_1/dbs/poolData01.dbf' RESIZE 3000M;

Comments powered by CComment