Keeping an eye on tablespace growth prevents surprise outages and ORA-01653/ORA-01654: unable to extend segment. The query below surfaces total / used / free (GB), % used, MAXSIZE (GB), and % of max for each PERMANENT tablespace—plus automation tips and a CDB/PDB variant.
Production-Ready Query (PERMANENT tablespaces)
WITH df AS (
SELECT tablespace_name,
SUM(bytes)/1024/1024/1024 total_gb,
SUM(maxbytes)/1024/1024/1024 max_gb
FROM dba_data_files
GROUP BY tablespace_name
),
fs AS (
SELECT tablespace_name,
SUM(bytes)/1024/1024/1024 free_gb
FROM dba_free_space
GROUP BY tablespace_name
)
SELECT t.tablespace_name,
t.contents,
t.bigfile,
ROUND(df.total_gb, 2) AS total_gb,
ROUND(df.total_gb - NVL(fs.free_gb, 0), 2) AS used_gb,
ROUND(NVL(fs.free_gb, 0), 2) AS free_gb,
ROUND((df.total_gb - NVL(fs.free_gb, 0)) / df.total_gb * 100, 2) AS pct_used,
ROUND(df.max_gb, 2) AS max_gb,
ROUND((df.total_gb - NVL(fs.free_gb, 0)) / NULLIF(df.max_gb, 0) * 100, 2) AS pct_of_max
FROM dba_tablespaces t
JOIN df ON df.tablespace_name = t.tablespace_name
LEFT JOIN fs ON fs.tablespace_name = t.tablespace_name
WHERE t.contents = 'PERMANENT'
ORDER BY pct_used DESC NULLS LAST;
What each column means
- total_gb: Current size of all datafiles in the tablespace.
- used_gb:
total_gb − free_gb(allocated blocks in use). - free_gb: Unallocated free space inside datafiles (
dba_free_space). - pct_used: Percent of current size in use.
- max_gb: Sum of each file’s
MAXBYTES(0 if not set / autoextend off). - pct_of_max: Utilization relative to theoretical maximum; NULL if
max_gb = 0.
Requirements
- Privileges:
SELECT_CATALOG_ROLE(or direct SELECT onDBA_%views). - Scope: Permanent tablespaces only (excludes TEMP/UNDO by design).
Run it in SQL*Plus / SQLcl with nice formatting
SET LINES 200 PAGES 200 TRIMSPOOL ON TAB OFF
COL tablespace_name FOR A28
COL contents FOR A10
COL bigfile FOR A7
COL total_gb FOR 999,990.00
COL used_gb FOR 999,990.00
COL free_gb FOR 999,990.00
COL pct_used FOR 990.00
COL max_gb FOR 999,990.00
COL pct_of_max FOR 990.00
-- paste the WITH…SELECT statement here
Export to CSV (great for Grafana/Excel)
SET FEEDBACK OFF HEADING ON COLSEP ,
SPOOL tablespace_usage.csv
-- paste the query
SPOOL OFF
Alerting thresholds
- Warning when
pct_used ≥ 85orfree_gb < 5 - Critical when
pct_used ≥ 95orfree_gb < 2
You can wrap the query and send mail from the host with a cron job:
# crontab (runs every hour)
0 * * * * sqlplus -s / as sysdba @/path/tablespace_usage.sql | \
awk -F, 'NR>1 && ($8>=85 || $6<5){flag=1} END{exit !flag}' && \
mail -s "Oracle TS space warning on $(hostname)" dba@yourdomain.com < /path/tablespace_usage.csv
Adjust $8/$6 if your CSV column order differs. Consider replacing mail with your SMTP wrapper or OEM job for enterprise alerting.
CDB/PDB variant (per-PDB view or all containers)
Run inside each PDB, or use CDB views to see all containers:
WITH df AS (
SELECT con_id, tablespace_name,
SUM(bytes)/1024/1024/1024 total_gb,
SUM(maxbytes)/1024/1024/1024 max_gb
FROM cdb_data_files
GROUP BY con_id, tablespace_name
),
fs AS (
SELECT con_id, tablespace_name,
SUM(bytes)/1024/1024/1024 free_gb
FROM cdb_free_space
GROUP BY con_id, tablespace_name
)
SELECT v.name AS pdb_name,
t.tablespace_name,
ROUND(df.total_gb,2) total_gb,
ROUND(df.total_gb - NVL(fs.free_gb,0),2) used_gb,
ROUND(NVL(fs.free_gb,0),2) free_gb,
ROUND((df.total_gb - NVL(fs.free_gb,0))/df.total_gb*100,2) pct_used,
ROUND(df.max_gb,2) max_gb,
ROUND((df.total_gb - NVL(fs.free_gb,0))/NULLIF(df.max_gb,0)*100,2) pct_of_max
FROM cdb_tablespaces t
JOIN df ON df.con_id = t.con_id AND df.tablespace_name = t.tablespace_name
LEFT JOIN fs ON fs.con_id = t.con_id AND fs.tablespace_name = t.tablespace_name
JOIN v$pdbs v ON v.con_id = t.con_id
WHERE t.contents = 'PERMANENT'
ORDER BY pct_used DESC NULLS LAST;
Troubleshooting notes
pct_of_maxis NULL: Datafiles likely AUTOEXTEND OFF or noMAXSIZE. Consider setting saneMAXSIZEper file.- Low free but large files: Could be fragmentation;
dba_free_spaceshows coalesced extents. Consider segment moves, SHRINK (where safe), or TS reorg. - TEMP space: Monitor separately via
v$temp_space_header/v$tempextent_pool. - Standby/ADG: Read-only works fine—connect with a user that has catalog access.
- Bigfile TS: Column
bigfilesurfaces this; be mindful of resize/autoextend behavior.
Latest Tablespace monitoring script using dba_tablespace_usage_metrics and dba_lmt_free_space
PROMPT Enter tablespace name or partial name (use % for all)
COLUMN tablespace_name FORMAT A25
COLUMN used_percent FORMAT 999.99
COLUMN total_gb FORMAT 999,999.99
COLUMN used_gb FORMAT 999,999.99
COLUMN free_gb FORMAT 999,999.99
COLUMN status FORMAT A10
COLUMN extent_management FORMAT A10
COLUMN segment_space_management FORMAT A10
WITH ts_data AS (
SELECT
tablespace_name,
SUM(bytes) AS total_bytes,
COUNT(*) AS file_count
FROM dba_data_files
GROUP BY tablespace_name
),
ts_usage AS (
SELECT
d.tablespace_name,
u.used_percent,
(u.tablespace_size * t.block_size) AS total_bytes,
(u.used_space * t.block_size) AS used_bytes,
((u.tablespace_size - u.used_space) * t.block_size) AS free_bytes
FROM dba_tablespace_usage_metrics u
JOIN dba_tablespaces t ON u.tablespace_name = t.tablespace_name
JOIN dba_tablespaces d ON d.tablespace_name = t.tablespace_name
),
ts_free AS (
SELECT
tablespace_name,
NVL(SUM(bytes),0) AS free_bytes
FROM dba_free_space
GROUP BY tablespace_name
),
ts_final AS (
SELECT
d.tablespace_name,
NVL(u.used_percent, 0) AS used_percent,
NVL(d.status, 'UNKNOWN') AS status,
d.extent_management,
d.segment_space_management,
NVL(u.total_bytes, a.total_bytes) AS total_bytes,
NVL(u.used_bytes, (a.total_bytes - f.free_bytes)) AS used_bytes,
NVL(f.free_bytes, 0) AS free_bytes
FROM dba_tablespaces d
LEFT JOIN ts_data a ON d.tablespace_name = a.tablespace_name
LEFT JOIN ts_free f ON d.tablespace_name = f.tablespace_name
LEFT JOIN ts_usage u ON d.tablespace_name = u.tablespace_name
WHERE d.tablespace_name LIKE UPPER('&&ts')
)
SELECT
tablespace_name,
TO_CHAR(used_percent, '990.99') || '%' AS used_percent,
ROUND(total_bytes/1024/1024/1024,2) AS total_gb,
ROUND(used_bytes/1024/1024/1024,2) AS used_gb,
ROUND(free_bytes/1024/1024/1024,2) AS free_gb,
status,
extent_management,
segment_space_management
FROM ts_final
ORDER BY used_percent DESC;
Comments 3