02 Tablespace Usage

oracle_dba_perf_toolkit/daily_dba/02_tablespace_usage.sql

Disclaimer: This script is provided for educational and learning purposes. Review it carefully, test in a non-production environment, confirm privileges, and validate impact before using it in production.

SET PAGESIZE 200
SET LINESIZE 220
SET TRIMSPOOL ON
SET VERIFY OFF
SET FEEDBACK ON
SET TAB OFF

COLUMN tablespace_name FORMAT A30
COLUMN used_percent FORMAT 999.99
COLUMN used_mb FORMAT 999,999,999.99
COLUMN tablespace_size_mb FORMAT 999,999,999.99

SELECT
    m.tablespace_name,
    ROUND(m.used_percent, 2) used_percent,
    ROUND(m.used_space * t.block_size / 1024 / 1024, 2) used_mb,
    ROUND(m.tablespace_size * t.block_size / 1024 / 1024, 2) tablespace_size_mb
FROM
    dba_tablespace_usage_metrics m
    JOIN dba_tablespaces t
      ON t.tablespace_name = m.tablespace_name
ORDER BY
    m.used_percent DESC;