8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Script: ts_free_space.sql
( Download Script )
-- ----------------------------------------------------------------------------------- -- File Name : https://oracle-base.com/dba/monitoring/ts_free_space.sql -- Author : Tim Hall -- Description : Displays a list of tablespaces and their used/full status. -- Requirements : Access to the DBA views. -- Call Syntax : @ts_free_space.sql -- Last Modified: 13-OCT-2012 - Created. Based on ts_full.sql -- 22-SEP-2017 - LINESIZE set. -- ----------------------------------------------------------------------------------- SET PAGESIZE 140 LINESIZE 200 COLUMN used_pct FORMAT A11 SELECT tablespace_name, size_mb, free_mb, max_size_mb, max_free_mb, TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct, RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct FROM ( SELECT a.tablespace_name, b.size_mb, a.free_mb, b.max_size_mb, a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb FROM (SELECT tablespace_name, TRUNC(SUM(bytes)/1024/1024) AS free_mb FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, TRUNC(SUM(bytes)/1024/1024) AS size_mb, TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ) ORDER BY tablespace_name; SET PAGESIZE 14