OracleStorage

How far each datafile can be shrunk

Compares each file's size with its high-water mark to show space you can give back with a RESIZE. Reads DBA_EXTENTS, so it can take a while on large databases.

Not yet verified. How scripts are tested

ora-datafile-shrink.sql

1CLEAR COLUMNS2SET LINESIZE 160 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN file_name  HEADING "FILE"       FORMAT A704COLUMN size_mb    HEADING "SIZE MB"    FORMAT 99,999,9905COLUMN hwm_mb     HEADING "HWM MB"     FORMAT 99,999,9906COLUMN reclaim_mb HEADING "RECLAIM MB" FORMAT 99,999,9907 8SELECT f.file_name,9       ROUND(f.bytes / 1048576) AS size_mb,10       CEIL(NVL(h.hwm_block, 1) * t.block_size / 1048576) AS hwm_mb,11       ROUND(f.bytes / 1048576) - CEIL(NVL(h.hwm_block, 1) * t.block_size / 1048576) AS reclaim_mb12FROM dba_data_files f13JOIN dba_tablespaces t ON t.tablespace_name = f.tablespace_name14LEFT JOIN (SELECT file_id, MAX(block_id + blocks - 1) AS hwm_block15           FROM dba_extents16           GROUP BY file_id) h ON h.file_id = f.file_id17ORDER BY reclaim_mb DESC;

Save it as ora-datafile-shrink.sql and run it with SQL> @ora-datafile-shrink.

Open in denrepo

More Oracle scripts: Storage

Datafile usage with a bar graphEvery datafile with its size, used space, max size and a ten-character usage bar (X = used, - = free). Free_MB is the largest free extent in the…
Database size three ways: allocated, used and totalAllocated datafile size, space actually used by segments, and the overall footprint including temp files, online redo logs and control files. All in…
Database size summary: total, used and freeOne line with the database name and its total size (datafiles, temp files and redo logs), used space and free space, rounded to whole GB.
Size of one schemaTotal segment size in GB for the schema you enter.
Tablespace usageUsed and free space in GB against the maximum each tablespace can autoextend to, with its type (permanent, temporary or undo), fullest first.
Datafiles with size and autoextend limitsEvery datafile, its current size, whether it can grow, and how far. Files with AUTOEXTEND NO in a busy tablespace are the ones that page you at night.