-- -----------------------------------------------------------------------------
-- Script: tablespace_utilization_exec_level.sql
-- Purpose: Optimized Oracle Tablespace & Segment Report
-- Tuning: PARALLEL(4), ARRAYSIZE 100, MATERIALIZED CTEs for max speed.
-- -----------------------------------------------------------------------------
SET PAGESIZE 5000
SET LINESIZE 250
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET TERMOUT ON
SET TRIMOUT ON
SET TRIMSPOOL ON
SET COLSEP ' '
SET ARRAYSIZE 100
CLEAR COLUMNS
PROMPT
PROMPT ================================================================================
PROMPT ORACLE DATABASE UTILIZATION REPORT
PROMPT ================================================================================
PROMPT
-- -----------------------------------------------------------------------------
-- DATABASE SUMMARY
-- -----------------------------------------------------------------------------
COLUMN DB_NAME FORMAT A20
COLUMN "Total Allocated (GB)" FORMAT 999,999,990.99 HEADING 'Total Allocated|Space (GB)'
COLUMN "Total Used (GB)" FORMAT 999,999,990.99 HEADING 'Total Used|Space (GB)'
COLUMN "Total Free (GB)" FORMAT 999,999,990.99 HEADING 'Total Free|Space (GB)'
COLUMN "Total Used Pct (%)" FORMAT 990.99 HEADING 'Total DB|Used Pct (%)'
SELECT
d.name AS DB_NAME,
ROUND(SUM(df.bytes) / 1073741824, 2) AS "Total Allocated (GB)",
ROUND(SUM(df.bytes - NVL(fs.bytes, 0)) / 1073741824, 2) AS "Total Used (GB)",
ROUND(SUM(NVL(fs.bytes, 0)) / 1073741824, 2) AS "Total Free (GB)",
ROUND((SUM(df.bytes - NVL(fs.bytes, 0)) / SUM(df.bytes)) * 100, 2) AS "Total Used Pct (%)"
FROM dba_data_files df
LEFT JOIN (
SELECT /*+ PARALLEL(4) MATERIALIZE */ file_id, SUM(bytes) AS bytes
FROM dba_free_space
GROUP BY file_id
) fs ON df.file_id = fs.file_id
CROSS JOIN v$database d
WHERE df.tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents != 'TEMPORARY')
GROUP BY d.name;
-- -----------------------------------------------------------------------------
-- 1. OVERALL TABLESPACE UTILIZATION
-- -----------------------------------------------------------------------------
PROMPT
PROMPT --------------------------------------------------------------------------------
PROMPT -- 1. Overall Tablespace Utilization (Summary) --
PROMPT --------------------------------------------------------------------------------
PROMPT
COLUMN TABLESPACE_NAME FORMAT A30
COLUMN STATUS FORMAT A10
COLUMN "Total Size (GB)" FORMAT 999,999,990.99 HEADING 'Allocated|Size (GB)'
COLUMN "Used Space (GB)" FORMAT 999,999,990.99 HEADING 'Used (HWM)|Space (GB)'
COLUMN "Segments Used (GB)" FORMAT 999,999,990.99 HEADING 'Segments|Used (GB)'
COLUMN "Free Space (GB)" FORMAT 999,999,990.99 HEADING 'Free|Space (GB)'
COLUMN "Allocated Pct Used (%)" FORMAT 990.99 HEADING 'Allocated|Pct Used (%)'
COLUMN "Max Possible Size (GB)" FORMAT 999,999,990.99 HEADING 'Max Possible|Size (GB)'
COLUMN "Max Possible Pct Used (%)" FORMAT 990.99 HEADING 'Max Possible|Pct Used (%)'
WITH
ts_list AS (
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE contents IN ('PERMANENT', 'UNDO')
),
seg_agg AS (
SELECT /*+ PARALLEL(4) MATERIALIZE */
tablespace_name, SUM(bytes) bytes
FROM dba_segments
GROUP BY tablespace_name
),
free_agg AS (
SELECT /*+ PARALLEL(4) MATERIALIZE */
tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name
),
df_agg AS (
SELECT /*+ PARALLEL(4) MATERIALIZE */
tablespace_name,
SUM(bytes) total_bytes,
SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END) max_bytes
FROM dba_data_files
GROUP BY tablespace_name
)
SELECT
t.tablespace_name,
t.status,
ROUND(df.total_bytes / 1073741824, 2) AS "Total Size (GB)",
ROUND((df.total_bytes - NVL(fa.bytes, 0)) / 1073741824, 2) AS "Used Space (GB)",
ROUND(NVL(sa.bytes, 0) / 1073741824, 2) AS "Segments Used (GB)",
ROUND(NVL(fa.bytes, 0) / 1073741824, 2) AS "Free Space (GB)",
ROUND(((df.total_bytes - NVL(fa.bytes, 0)) / df.total_bytes) * 100, 2) AS "Allocated Pct Used (%)",
ROUND(df.max_bytes / 1073741824, 2) AS "Max Possible Size (GB)",
ROUND(((df.total_bytes - NVL(fa.bytes, 0)) / df.max_bytes) * 100, 2) AS "Max Possible Pct Used (%)"
FROM ts_list t
JOIN df_agg df ON t.tablespace_name = df.tablespace_name
LEFT JOIN free_agg fa ON t.tablespace_name = fa.tablespace_name
LEFT JOIN seg_agg sa ON t.tablespace_name = sa.tablespace_name
ORDER BY "Allocated Pct Used (%)" DESC;
-- -----------------------------------------------------------------------------
-- 2. TABLESPACE UTILIZATION BY DATAFILE
-- -----------------------------------------------------------------------------
PROMPT
PROMPT --------------------------------------------------------------------------------
PROMPT -- 2. Tablespace Utilization by Datafile --
PROMPT --------------------------------------------------------------------------------
PROMPT
COLUMN TABLESPACE_NAME FORMAT A25
COLUMN FILE_NAME FORMAT A60
COLUMN "Current Size (GB)" FORMAT 99,990.99 HEADING 'Current|Size (GB)'
COLUMN "Used Space (GB)" FORMAT 99,990.99 HEADING 'Used|Space (GB)'
COLUMN "Free Space (GB)" FORMAT 99,990.99 HEADING 'Free|Space (GB)'
COLUMN "Used Pct (%)" FORMAT 990.99 HEADING 'Used|Pct (%)'
COLUMN "Autoextensible" FORMAT A10 HEADING 'Auto?'
COLUMN "Max Size (GB)" FORMAT 99,990.99 HEADING 'Max Possible|Size (GB)'
SELECT
df.tablespace_name,
df.file_name,
ROUND(df.bytes / 1073741824, 2) AS "Current Size (GB)",
ROUND((df.bytes - NVL(fs.bytes, 0)) / 1073741824, 2) AS "Used Space (GB)",
ROUND(NVL(fs.bytes, 0) / 1073741824, 2) AS "Free Space (GB)",
ROUND(((df.bytes - NVL(fs.bytes, 0)) / df.bytes) * 100, 2) AS "Used Pct (%)",
df.autoextensible,
CASE
WHEN df.autoextensible = 'YES' THEN ROUND(df.maxbytes / 1073741824, 2)
ELSE NULL
END AS "Max Size (GB)"
FROM dba_data_files df
LEFT JOIN (
SELECT /*+ PARALLEL(4) */ file_id, SUM(bytes) AS bytes
FROM dba_free_space
GROUP BY file_id
) fs ON df.file_id = fs.file_id
ORDER BY df.tablespace_name, df.file_id;
-- -----------------------------------------------------------------------------
-- 3. SEGMENT LEVEL UTILIZATION (TOP 20)
-- -----------------------------------------------------------------------------
PROMPT
PROMPT --------------------------------------------------------------------------------
PROMPT -- 3. Segment Level Utilization (Top 20 Largest Objects) --
PROMPT --------------------------------------------------------------------------------
PROMPT
COLUMN OWNER FORMAT A20
COLUMN SEGMENT_NAME FORMAT A30
COLUMN PARTITION_NAME FORMAT A25
COLUMN SEGMENT_TYPE FORMAT A15
COLUMN TABLESPACE_NAME FORMAT A20
COLUMN "Size (GB)" FORMAT 999,999.99 HEADING 'Allocated|Size (GB)'
SELECT * FROM (
SELECT /*+ PARALLEL(s 4) */
s.owner,
s.segment_name,
s.partition_name,
s.segment_type,
s.tablespace_name,
ROUND(s.bytes / 1073741824, 2) AS "Size (GB)"
FROM dba_segments s
WHERE s.owner NOT IN (
'SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','APPQOSSYS','GSMADMIN_INTERNAL',
'LBACSYS','DIP','ORACLE_OCM','AUDSYS','XDB','MDSYS','OLAPSYS','ORDDATA',
'ORDPLUGINS','ORDSYS','WMSYS','EXFSYS','CTXSYS','ANONYMOUS',
'FLOWS_FILES','MGMT_VIEW','XS$NULL','MDDATA','OJVMSYS','APEX_PUBLIC_USER',
'GGSYS','DVF','DVSYS','LBAC_SYS','CDB_ONLY_SYS'
)
AND s.owner NOT LIKE 'APEX_%'
ORDER BY s.bytes DESC
)
WHERE ROWNUM <= 20;
-- -----------------------------------------------------------------------------
-- 4. TEMPORARY TABLESPACE (DBA_TEMP_FREE_SPACE)
-- -----------------------------------------------------------------------------
PROMPT
PROMPT --------------------------------------------------------------------------------
PROMPT -- 4. Temporary Tablespace Utilization (Current) --
PROMPT --------------------------------------------------------------------------------
PROMPT
COLUMN TABLESPACE_NAME FORMAT A25
COLUMN "Allocated Size (GB)" FORMAT 99,990.99 HEADING 'Allocated|Size (GB)'
COLUMN "Used Space (GB)" FORMAT 99,990.99 HEADING 'Used|Space (GB)'
COLUMN "Free Space (GB)" FORMAT 99,990.99 HEADING 'Free|Space (GB)'
COLUMN "Used Pct (%)" FORMAT 990.99 HEADING 'Used|Pct (%)'
COLUMN "Max Possible Size (GB)" FORMAT 99,990.99 HEADING 'Max Possible|Size (GB)'
SELECT
fs.tablespace_name,
ROUND(fs.allocated_space / 1073741824, 2) AS "Allocated Size (GB)",
ROUND((fs.allocated_space - fs.free_space) / 1073741824, 2) AS "Used Space (GB)",
ROUND(fs.free_space / 1073741824, 2) AS "Free Space (GB)",
ROUND(((fs.allocated_space - fs.free_space) / fs.allocated_space) * 100, 2) AS "Used Pct (%)",
ROUND(tf.max_bytes / 1073741824, 2) AS "Max Possible Size (GB)"
FROM dba_temp_free_space fs
LEFT JOIN (
SELECT tablespace_name, SUM(DECODE(autoextensible, 'YES', maxbytes, bytes)) AS max_bytes
FROM dba_temp_files
GROUP BY tablespace_name
) tf ON fs.tablespace_name = tf.tablespace_name
ORDER BY "Used Pct (%)" DESC;
-- -----------------------------------------------------------------------------
-- 5. REAL-TIME TEMP USAGE
-- -----------------------------------------------------------------------------
PROMPT
PROMPT --------------------------------------------------------------------------------
PROMPT -- 5. Real-Time Temporary Tablespace Usage (Active Sorts/Hashes) --
PROMPT --------------------------------------------------------------------------------
PROMPT
COLUMN TABLESPACE_NAME FORMAT A25
COLUMN "Total Temp Size (GB)" FORMAT 99,990.99 HEADING 'Total Temp|Size (GB)'
COLUMN "Used Temp Space (GB)" FORMAT 99,990.99 HEADING 'Used Temp|Space (GB)'
COLUMN "Free Temp Space (GB)" FORMAT 99,990.99 HEADING 'Free Temp|Space (GB)'
COLUMN "Used Pct (%)" FORMAT 990.99 HEADING 'Used|Pct (%)'
SELECT
ts.tablespace_name,
ROUND(SUM(tf.bytes) / 1073741824, 2) AS "Total Temp Size (GB)",
ROUND(NVL(tu.used_space, 0) / 1073741824, 2) AS "Used Temp Space (GB)",
ROUND((SUM(tf.bytes) - NVL(tu.used_space, 0)) / 1073741824, 2) AS "Free Temp Space (GB)",
ROUND((NVL(tu.used_space, 0) / SUM(tf.bytes)) * 100, 2) AS "Used Pct (%)"
FROM dba_temp_files tf
JOIN dba_tablespaces ts ON tf.tablespace_name = ts.tablespace_name
LEFT JOIN (
SELECT tablespace_name, SUM(bytes_used) AS used_space
FROM gv$temp_space_header
GROUP BY tablespace_name
) tu ON ts.tablespace_name = tu.tablespace_name
WHERE ts.contents = 'TEMPORARY'
GROUP BY ts.tablespace_name, tu.used_space
ORDER BY ts.tablespace_name;
PROMPT
PROMPT --------------------------------------------------------------------------------
PROMPT -- Execution completed. --
PROMPT --------------------------------------------------------------------------------
PROMPT
SET FEEDBACK ON
===============================================================
-- Fast Oracle Tablespace & DB Storage Overview (2024-2025 style)
-- Goal: under 5-10 seconds even on big databases
set pagesize 5000 linesize 220 trimspool on feedback off verify off
set colsep ' '
column ts_name format a38 heading "Tablespace Name"
column status format a8
column contents format a9
column alloc_gb format 999,999.9 heading "Alloc|GB"
column used_gb format 999,999.9 heading "Used|GB"
column free_gb format 999,999.9 heading "Free|GB"
column pct_used format 990.0 heading "Used|%"
column autoext_max_gb format 999,999.9 heading "Autoext|Max GB"
column files format 999 heading "Files"
PROMPT
PROMPT ===============================================================================
PROMPT Fast Tablespace Utilization Report (excluding TEMP)
PROMPT ===============================================================================
PROMPT
WITH
ts AS (
select /*+ materialize */
tablespace_name, status, contents, bigfile
from dba_tablespaces
),
df AS (
select /*+ materialize no_merge */
tablespace_name,
count(*) files,
sum(bytes)/1024/1024/1024 alloc_gb,
sum(decode(autoextensible,'YES',greatest(bytes,maxbytes),bytes))
/1024/1024/1024 autoext_max_gb
from dba_data_files
group by tablespace_name
),
fs AS (
-- The only slow part - but we do it only once
select /*+ materialize */
tablespace_name,
sum(bytes)/1024/1024/1024 free_gb
from dba_free_space
group by tablespace_name
)
SELECT
t.tablespace_name ts_name,
t.status,
t.contents,
df.alloc_gb,
ROUND(df.alloc_gb - nvl(fs.free_gb,0),1) used_gb,
nvl(fs.free_gb,0) free_gb,
ROUND( (df.alloc_gb - nvl(fs.free_gb,0)) / df.alloc_gb * 100, 1) pct_used,
ROUND(df.autoext_max_gb,1) autoext_max_gb,
df.files
FROM ts t
JOIN df ON t.tablespace_name = df.tablespace_name
LEFT JOIN fs ON t.tablespace_name = fs.tablespace_name
WHERE t.contents != 'TEMPORARY'
ORDER BY pct_used DESC, t.tablespace_name;
-- Quick TEMP summary (usually fast enough)
PROMPT
PROMPT Temporary Tablespaces - Quick View:
PROMPT ----------------------------------
column temp_used_pct format 990.0 heading "Used|%"
column temp_alloc_gb format 999,999.9
column temp_max_gb format 999,999.9
SELECT
tf.tablespace_name,
ROUND(SUM(tf.bytes)/1024/1024/1024,1) temp_alloc_gb,
ROUND(SUM(greatest(tf.bytes, tf.maxbytes))/1024/1024/1024,1) temp_max_gb,
ROUND(NVL(tu.used_bytes,0) / SUM(tf.bytes) * 100,1) temp_used_pct
FROM dba_temp_files tf
LEFT JOIN (
SELECT tablespace_name, SUM(bytes_used) used_bytes
FROM gv$temp_space_header
GROUP BY tablespace_name
) tu USING (tablespace_name)
GROUP BY tf.tablespace_name, tu.used_bytes
ORDER BY temp_used_pct DESC;
PROMPT
PROMPT Total Database Size (datafiles only):
PROMPT -------------------------------------
select round(sum(bytes)/1024/1024/1024,1) "Total Datafiles GB"
from dba_data_files;
set feedback on
No comments:
Post a Comment