Oracle CDB/PDB Space & Fragmentation HTML Email Report

Oracle CDB/PDB Space & Fragmentation HTML Email Report

A practical guide to setup, usage, and why it’s useful (with full source code)

Why this report?

Keeping a close eye on database space usage, growth, and fragmentation (tables and LOBs) pays off in reduced storage cost, faster backups, and fewer fire-fights during peak load. This script mails a beautiful, color‑coded HTML report you can schedule daily. It works at PDB scope while also surfacing key AWR/Perf settings at CDB scope, so you get the full picture in one glance.

What the script does

• Sources your Oracle env file (~/<CDB>.env)
• Connects as SYSDBA, captures root-level AWR settings, and then switches to your PDB
• Generates an HTML email via sendmail with these sections:

1. Top 20 Largest Objects (segments)
2. Tablespace Utilization (Allocated vs Max with autoextend, % color-coded)
3. Temp TS Utilization
4. SYSAUX Utilization + Top Occupants
5. Table Fragmentation (computed from DBA_TABLES stats) with auto-detected block size
6. LOB Fragmentation (estimated from DBA_LOBS, DBA_TAB_COLS, DBA_SEGMENTS)
• Includes SecureFile vs BasicFile, RETENTION/PCTVERSION, CHUNK
• Recommended Action column: SHRINK/COALESCE/MIGRATE guidance
7. AWR & Perf Settings (CDB): Block size, statistics_level, mgmt pack access, retention/interval, snapshot count (last 7d)

Color coding

• Tablespace Used% Max:
• ✅ Green <70% | 🟨 Amber 70–85% | 🔴 Red >85%
• Fragmentation (Table & LOB Reclaimable%):
• 🔴 ≥90% | 🟨 ≥ THRESHOLD (default 50%) | ✅ below threshold

---

Prerequisites

• Oracle Client tools accessible (i.e., sqlplus in PATH)
• OS user can run sqlplus "/ as sysdba"
• Env file: ~/<CDB>.env sets ORACLE_HOME, ORACLE_SID, and updates PATH
• sendmail installed and executable (default /usr/sbin/sendmail)
• Recommended privileges to query:
• SELECT on: DBA_* views used (DBA_TABLES, DBA_SEGMENTS, DBA_TAB_COLS, DBA_LOBS, DBA_FREE_SPACE, DBA_DATA_FILES, DBA_TEMP_FILES, DBA_TEMP_FREE_SPACE, V$INSTANCE, V$PARAMETER, DBA_HIST_*)
• Typically available when connecting as SYSDBA

Tip: The table/LOB fragmentation estimates rely on fresh statistics. Use DBMS_STATS.GATHER_SCHEMA_STATS on critical schemas regularly.

---

Quick start

1. Save the script below as oracle_space_report.sh
2. Make executable:
   
   chmod +x oracle_space_report.sh
3. Run:
   ./oracle_space_report.sh -c CDB1 -p YOUR_PDB -t dba@yourcompany.com -s "Daily Space & Fragmentation Report"

   
   

Options

• -c CDB name (used to source ~/<CDB>.env)
• -p PDB name to target
• -t Comma-separated recipients (e.g., dba@x.com,ops@x.com)
• -s Subject (optional)
• -f From email (optional)
• -T Fragmentation amber threshold % (default 50)

---

How it works (under the hood)

• Uses sqlplus once, printing HTML table rows directly from SQL/PLSQL
• Switches container to your PDB for all space metrics
• Tablespace utilization considers MAXBYTES when autoextend is enabled
• Table fragmentation: estimates from DBA_TABLES stats (BLOCKS, NUM_ROWS, AVG_ROW_LEN) with auto-detected db_block_size.
• Reclaimable% formula is a heuristic; includes a -10% offset (industry-friendly bias to avoid false positives)
• LOB fragmentation: estimates actual LOB payload (AVG_COL_LEN * NUM_ROWS) against actual LOB segment bytes from DBA_SEGMENTS
• Recommended Action:
• Tables: SHRINK TABLE/SHRINK PARTITION; COALESCE INDEX(ES)
• LOBs: If SecureFile → SHRINK; if BasicFile → Migrate to SecureFile then SHRINK; always review RETENTION/PCTVERSION

---

Scheduling (cron)

# Every day at 07:15

15 7 * * * /opt/dba/oracle_space_report.sh -c CDB1 -p YOUR_PDB -t dba@yourcompany.com -s "Daily Space & Frag" -T 50 >/dev/null 2>&1

---

Troubleshooting

• sqlplus not found → Ensure ORACLE_HOME/bin in PATH inside ~/<CDB>.env
• PDB section shows CDB$ROOT → PDB might be closed; open it or check the name
• No fragmentation rows → Stats may be stale; gather stats; or lower -T
• Email not received → Confirm sendmail path; check mail relay/firewall

---

Handy commands (when taking action)

Enable row movement + shrink (non-partitioned)

ALTER TABLE owner.table_name ENABLE ROW MOVEMENT;

ALTER TABLE owner.table_name SHRINK SPACE;

Partitioned table

ALTER TABLE owner.table_name ENABLE ROW MOVEMENT;

ALTER TABLE owner.table_name MODIFY PARTITION part_name SHRINK SPACE;

Indexes

ALTER INDEX owner.index_name COALESCE;

-- or

ALTER INDEX owner.index_name REBUILD ONLINE;

SecureFile LOB

ALTER TABLE owner.table_name MODIFY (lob(colname) (SHRINK SPACE));

Migrate BasicFile → SecureFile (example)

ALTER TABLE owner.table_name MOVE LOB (colname) STORE AS SECUREFILE (TABLESPACE users);

-- Then shrink as above

Always validate in non-prod and prefer maintenance windows for SHRINK/REBUILD operations.

---

Full Source Code (HTML + Color-coded + AWR + LOB + Auto Block Size)

#!/usr/bin/env bash

#

# Oracle CDB/PDB Space & Fragmentation Report (HTML, color-coded)

# Sends a styled HTML email via sendmail.

#

# Author: DBA

# Usage: oracle_space_report.sh -c <CDB_NAME> -p <PDB_NAME> -t <to_email> [-s <subject>] [-f <from_email>] [-T <frag_threshold_pct>]

#

set -euo pipefail

# -------- Defaults --------

SUBJECT=""

FROM_EMAIL="oracle@$(hostname -f 2>/dev/null || hostname)"

THRESHOLD=50 # Amber threshold for fragmentation %

# -------- Usage --------

usage() {

cat <<EOF

Usage: $0 -c <CDB_NAME> -p <PDB_NAME> -t <to_email> [-s <subject>] [-f <from_email>] [-T <frag_threshold_pct>]

Required:

-c CDB name (used to source ~/<CDB>.env)

-p PDB name (ALTER SESSION SET CONTAINER=<PDB>)

-t Comma-separated recipient email(s) for sendmail (e.g. dba@company.com,ops@company.com)

Optional:

-s Mail subject (default: "Oracle Space Report: <CDB>/<PDB> on <HOSTNAME>")

-f From email (default: $FROM_EMAIL)

-T Fragmentation threshold percentage (default: $THRESHOLD)

EOF

exit 1

}

# -------- Args --------

CDB_NAME=""; PDB_NAME=""; TO_EMAIL=""

while getopts ":c:p:t:s:f:T:h" opt; do

case "$opt" in

c) CDB_NAME="$OPTARG" ;;

p) PDB_NAME="$OPTARG" ;;

t) TO_EMAIL="$OPTARG" ;;

s) SUBJECT="$OPTARG" ;;

f) FROM_EMAIL="$OPTARG" ;;

T) THRESHOLD="$OPTARG" ;;

h|*) usage ;;

esac

done

[[ -z "$CDB_NAME" || -z "$PDB_NAME" || -z "$TO_EMAIL" ]] && usage

# Use ~/<CDB>.env

ENV_FILE="$HOME/${CDB_NAME}.env"

[[ -r "$ENV_FILE" ]] || { echo "ERROR: Env file not found or not readable: $ENV_FILE"; exit 2; }

# shellcheck source=/dev/null

. "$ENV_FILE"

command -v sqlplus >/dev/null 2>&1 || { echo "ERROR: sqlplus not found in PATH. Check $ENV_FILE"; exit 3; }

SENDMAIL_BIN=${SENDMAIL_BIN:-/usr/sbin/sendmail}

[[ -x "$SENDMAIL_BIN" ]] || { echo "ERROR: sendmail not found at $SENDMAIL_BIN"; exit 4; }

HOSTNAME_FQDN=$(hostname -f 2>/dev/null || hostname)

DATE_STR=$(date +"%Y-%m-%d %H:%M:%S %Z")

TMP_REPORT=$(mktemp /tmp/ora_space_rpt.XXXXXX.html)

trap 'rm -f "$TMP_REPORT"' EXIT

if [[ -z "$SUBJECT" ]]; then

SUBJECT="Oracle Space Report: ${CDB_NAME}/${PDB_NAME} on ${HOSTNAME_FQDN}"

fi

# -------- SQL*Plus Execution (emit HTML for sections) --------

sqlplus -s "/ as sysdba" > "$TMP_REPORT" <<SQL

set termout off pages 0 lines 4000 trimspool on trims on feedback off verify off heading off

set define off

set escape off

set serveroutput on size unlimited

whenever sqlerror exit sql.sqlcode

prompt <div class="header">

prompt <h1>Oracle Space and Fragmentation Report</h1>

prompt <div class="meta">

prompt <span class="badge">Host: ${HOSTNAME_FQDN}</span>

prompt <span class="badge">CDB: ${CDB_NAME}</span>

prompt <span class="badge">PDB: ${PDB_NAME}</span>

prompt <span class="badge">Generated: ${DATE_STR}</span>

select '<span class="badge">Version: '||version||'</span>' from v\$instance;

prompt <span class="badge">Frag Threshold: ${THRESHOLD}%</span>

prompt </div>

prompt </div>

--------------------------------------------------------------------------------

prompt <div class="section">

prompt <h2>0) AWR & Performance Settings (CDB scope)</h2>

prompt <table class="table">

prompt <thead><tr><th>Setting</th><th>Value</th></tr></thead><tbody>

-- DB Block Size

select '<tr><td>DB Block Size</td><td>'||to_char(round(value/1024))||' KB</td></tr>'

from v\$parameter where name='db_block_size';

-- Statistics Level

select '<tr><td>Statistics Level</td><td>'||value||'</td></tr>'

from v\$parameter where name='statistics_level';

-- Control Management Pack Access

select '<tr><td>Control Mgmt Pack Access</td><td>'||value||'</td></tr>'

from v\$parameter where name='control_management_pack_access';

-- AWR Retention & Snapshot Interval (INTERVAL DAY TO SECOND)

select '<tr><td>AWR Retention</td><td>'||

(extract(day from retention))||' d '||

lpad(extract(hour from retention),2,'0')||':'||

lpad(extract(minute from retention),2,'0')||'</td></tr>'

from dba_hist_wr_control

where rownum=1;

select '<tr><td>AWR Snapshot Interval</td><td>'||

(extract(day from snap_interval))||' d '||

lpad(extract(hour from snap_interval),2,'0')||':'||

lpad(extract(minute from snap_interval),2,'0')||'</td></tr>'

from dba_hist_wr_control

where rownum=1;

-- Snapshot count (last 7 days)

select '<tr><td>AWR Snapshots (last 7 days)</td><td>'||count(*)||'</td></tr>'

from dba_hist_snapshot

where begin_interval_time >= (sysdate - 7);

prompt </tbody></table>

prompt </div>

-- Now switch to the requested PDB for PDB-level space reporting

alter session set container=${PDB_NAME};

--------------------------------------------------------------------------------

prompt <div class="section">

prompt <h2>1) Top 20 Largest Objects</h2>

prompt <table class="table">

prompt <thead><tr>

prompt <th>Owner</th><th>Segment</th><th>Type</th><th>Tablespace</th><th class="num">Size (GB)</th>

prompt </tr></thead><tbody>

select

'<tr><td>'||

replace(replace(replace(owner,'&','&amp;'),'<','&lt;'),'>','&gt;')||'</td><td>'||

replace(replace(replace(segment_name,'&','&amp;'),'<','&lt;'),'>','&gt;')||'</td><td>'||

replace(replace(replace(segment_type,'&','&amp;'),'<','&lt;'),'>','&gt;')||'</td><td>'||

replace(replace(replace(tablespace_name,'&','&amp;'),'<','&lt;'),'>','&gt;')||'</td><td class="num">'||

to_char(round(bytes/1024/1024/1024,2),'999,990.00')||'</td></tr>'

from (

select owner, segment_name, segment_type, tablespace_name, bytes

from dba_segments

where owner not in ('SYS','SYSTEM')

order by bytes desc

)

where rownum <= 20;

prompt </tbody>

prompt </table>

prompt </div>

--------------------------------------------------------------------------------

prompt <div class="section">

prompt <h2>2) Tablespace Utilization (Permanent)</h2>

prompt <div class="small">Row color by Used% of Max: <span class="chip ok">OK &lt; 70%</span> <span class="chip amber">70–85%</span> <span class="chip red">&gt; 85%</span></div>

prompt <table class="table">

prompt <thead><tr>

prompt <th>Tablespace</th><th class="num">Allocated (GB)</th><th class="num">Max (GB)</th>

prompt <th class="num">Used (GB)</th><th class="num">Free (GB)</th>

prompt <th class="num">Used% Alloc</th><th class="num">Used% Max</th>

prompt </tr></thead><tbody>

with df as (

select tablespace_name,

sum(bytes) alloc_bytes,

sum(case when autoextensible='YES' then maxbytes else bytes end) max_bytes

from dba_data_files

group by tablespace_name

),

fs as (

select tablespace_name, sum(bytes) free_bytes

from dba_free_space

group by tablespace_name

),

j as (

select df.tablespace_name,

df.alloc_bytes,

df.max_bytes,

nvl(fs.free_bytes,0) as free_bytes,

(df.alloc_bytes - nvl(fs.free_bytes,0)) as used_bytes

from df left join fs on df.tablespace_name = fs.tablespace_name

)

select

'<tr class="'||

case

when (100*used_bytes/nullif(max_bytes,0)) > 85 then 'sev-red'

when (100*used_bytes/nullif(max_bytes,0)) >= 70 then 'sev-amber'

else 'sev-ok'

end

||'"><td>'||

replace(replace(replace(tablespace_name,'&','&amp;'),'<','&lt;'),'>','&gt;')||'</td><td class="num">'||

to_char(round(alloc_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(max_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(used_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(free_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(100*used_bytes/nullif(alloc_bytes,0),2),'990.00')||'</td><td class="num">'||

to_char(round(100*used_bytes/nullif(max_bytes,0),2),'990.00')||'</td></tr>'

from j

order by (100*used_bytes/nullif(max_bytes,0)) desc nulls last;

prompt </tbody>

prompt </table>

prompt </div>

--------------------------------------------------------------------------------

prompt <div class="section">

prompt <h2>3) Temp Tablespace Utilization</h2>

prompt <table class="table">

prompt <thead><tr>

prompt <th>Temporary TS</th><th class="num">Allocated (GB)</th><th class="num">Max (GB)</th>

prompt <th class="num">Used (GB)</th><th class="num">Free (GB)</th>

prompt <th class="num">Used% Alloc</th><th class="num">Used% Max</th>

prompt </tr></thead><tbody>

with tf as (

select tablespace_name,

sum(bytes) alloc_bytes,

sum(case when autoextensible='YES' then maxbytes else bytes end) max_bytes

from dba_temp_files

group by tablespace_name

),

tfree as (

select tablespace_name, sum(free_space) free_bytes

from dba_temp_free_space

group by tablespace_name

),

j as (

select tf.tablespace_name,

tf.alloc_bytes,

tf.max_bytes,

nvl(tfree.free_bytes,0) as free_bytes,

(tf.alloc_bytes - nvl(tfree.free_bytes,0)) as used_bytes

from tf left join tfree on tf.tablespace_name = tfree.tablespace_name

)

select

'<tr class="'||

case

when (100*used_bytes/nullif(max_bytes,0)) > 85 then 'sev-red'

when (100*used_bytes/nullif(max_bytes,0)) >= 70 then 'sev-amber'

else 'sev-ok'

end

||'"><td>'||

replace(replace(replace(tablespace_name,'&','&amp;'),'<','&lt;'),'>','&gt;')||'</td><td class="num">'||

to_char(round(alloc_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(max_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(used_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(free_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(100*used_bytes/nullif(alloc_bytes,0),2),'990.00')||'</td><td class="num">'||

to_char(round(100*used_bytes/nullif(max_bytes,0),2),'990.00')||'</td></tr>'

from j

order by (100*used_bytes/nullif(max_bytes,0)) desc nulls last;

prompt </tbody>

prompt </table>

prompt </div>

--------------------------------------------------------------------------------

prompt <div class="section">

prompt <h2>4) SYSAUX Utilization</h2>

prompt <table class="table">

prompt <thead><tr>

prompt <th>Tablespace</th><th class="num">Allocated (GB)</th><th class="num">Max (GB)</th>

prompt <th class="num">Used (GB)</th><th class="num">Free (GB)</th><th class="num">Used% Alloc</th>

prompt </tr></thead><tbody>

with df as (

select tablespace_name,

sum(bytes) alloc_bytes,

sum(case when autoextensible='YES' then maxbytes else bytes end) max_bytes

from dba_data_files

where tablespace_name='SYSAUX'

group by tablespace_name

),

fs as (

select tablespace_name, sum(bytes) free_bytes

from dba_free_space

where tablespace_name='SYSAUX'

group by tablespace_name

),

j as (

select df.tablespace_name,

df.alloc_bytes,

df.max_bytes,

nvl(fs.free_bytes,0) as free_bytes,

(df.alloc_bytes - nvl(fs.free_bytes,0)) as used_bytes

from df left join fs on df.tablespace_name = fs.tablespace_name

)

select

'<tr><td>SYSAUX</td><td class="num">'||

to_char(round(alloc_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(max_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(used_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(free_bytes/1024/1024/1024,2),'999,990.00')||'</td><td class="num">'||

to_char(round(100*used_bytes/nullif(alloc_bytes,0),2),'990.00')||'</td></tr>'

from j;

prompt </tbody>

prompt </table>

prompt <h3>SYSAUX Top Occupants</h3>

prompt <table class="table">

prompt <thead><tr><th>Occupant</th><th class="num">MB</th></tr></thead><tbody>

select

'<tr><td>'||

replace(replace(replace(occupant_name,'&','&amp;'),'<','&lt;'),'>','&gt;')||'</td><td class="num">'||

to_char(round(space_usage_kbytes/1024,2),'999,999,990.00')||'</td></tr>'

from (

select occupant_name, space_usage_kbytes

from v\$sysaux_occupants

where space_usage_kbytes is not null

order by space_usage_kbytes desc

)

where rownum <= 15;

prompt </tbody>

prompt </table>

prompt </div>

--------------------------------------------------------------------------------

prompt <div class="section">

prompt <h2>5) Table Fragmentation (by DBA_TABLES stats)</h2>

prompt <div class="small">Color by Reclaimable%: <span class="chip red">&ge; 90%</span> <span class="chip amber">&ge; ${THRESHOLD}%</span> <span class="chip ok">&lt; ${THRESHOLD}%</span></div>

prompt <table class="table">

prompt <thead><tr>

prompt <th>Owner</th><th>Table</th><th>Partitioned</th>

prompt <th class="num">Size (GB)</th><th class="num">Actual Data (GB)</th>

prompt <th class="num">Wasted Space (GB)</th><th class="num">Reclaimable %</th><th>Recommended Action</th>

prompt </tr></thead><tbody>

with p as (

select to_number(value) as block_size from v\$parameter where name='db_block_size'

),

t as (

select owner, table_name, partitioned, nvl(blocks,0) blocks, nvl(num_rows,0) num_rows, nvl(avg_row_len,0) avg_row_len

from dba_tables

where owner not in ('SYS','SYSTEM','XDB','SYSMAN','MDSYS','CTXSYS','DBSNMP','OUTLN','GSMADMIN_INTERNAL','ORDSYS','ORDDATA','AUDSYS')

),

calc as (

select

owner, table_name, partitioned,

(blocks * (select block_size from p)) as bytes_alloc,

(num_rows * avg_row_len) as bytes_data,

case when blocks > 0 then greatest(0, (blocks * (select block_size from p)) - (num_rows * avg_row_len)) else 0 end as bytes_wasted,

case when (blocks * (select block_size from p)) > 0 then

greatest(0, round( ( ((blocks*(select block_size from p)/1024) - (num_rows*avg_row_len/1024)) / (blocks*(select block_size from p)/1024) ) * 100 - 10, 2 ))

else null end as pct_reclaim

from t

where (blocks * (select block_size from p)) > (num_rows * avg_row_len)

)

select

'<tr class="'||

case when pct_reclaim >= 90 then 'sev-red'

when pct_reclaim >= ${THRESHOLD} then 'sev-amber'

else 'sev-ok' end

||'"><td>'||replace(replace(replace(owner,'&','&amp;'),'<','&lt;'),'>','&gt;')||

'</td><td>'||replace(replace(replace(table_name,'&','&amp;'),'<','&lt;'),'>','&gt;')||

'</td><td>'||replace(replace(replace(partitioned,'&','&amp;'),'<','&lt;'),'>','&gt;')||

'</td><td class="num">'||to_char(round(bytes_alloc/1024/1024/1024,2),'999,990.00')||

'</td><td class="num">'||to_char(round(bytes_data/1024/1024/1024,2),'999,990.00')||

'</td><td class="num">'||to_char(round(bytes_wasted/1024/1024/1024,2),'999,990.00')||

'</td><td class="num">'||nvl(to_char(pct_reclaim,'990.00'),'')||

'</td><td>'||

case

when pct_reclaim is null or pct_reclaim < ${THRESHOLD} then 'MONITOR'

when partitioned = 'YES' then 'SHRINK PARTITION (ENABLE ROW MOVEMENT); COALESCE INDEX PARTITIONS'

else 'SHRINK TABLE (ENABLE ROW MOVEMENT); COALESCE INDEXES'

end

||'</td></tr>'

from calc

order by bytes_wasted desc

fetch first 50 rows only;

prompt </tbody>

prompt </table>

prompt </div>

--------------------------------------------------------------------------------

prompt <div class="section">

prompt <h2>6) LOB Fragmentation (estimated from stats)</h2>

prompt <div class="small">

prompt Estimated using DBA_LOBS + DBA_TAB_COLS (AVG_COL_LEN) * DBA_TABLES.NUM_ROWS vs. LOB segment bytes.

prompt SecureFile supports SHRINK; BasicFile typically requires migration to SecureFile to reclaim space.

prompt </div>

prompt <table class="table">

prompt <thead><tr>

prompt <th>Owner</th><th>Table</th><th>Column</th><th>SecureFile</th><th>Partitioned</th>

prompt <th>TS</th><th class="num">Chunk (KB)</th><th class="num">PCTVERSION</th><th class="num">RETENTION</th>

prompt <th class="num">LOB Size (GB)</th><th class="num">Est Data (GB)</th><th class="num">Wasted (GB)</th><th class="num">Reclaimable %</th>

prompt <th>Recommended Action</th>

prompt </tr></thead><tbody>

with p as (

select to_number(value) as block_size from v\$parameter where name='db_block_size'

),

t as (

select owner, table_name, nvl(num_rows,0) num_rows

from dba_tables

where owner not in ('SYS','SYSTEM','XDB','SYSMAN','MDSYS','CTXSYS','DBSNMP','OUTLN','GSMADMIN_INTERNAL','ORDSYS','ORDDATA','AUDSYS')

),

c as (

select owner, table_name, column_name, nvl(avg_col_len,0) avg_col_len

from dba_tab_cols

where data_type in ('BLOB','CLOB','NCLOB')

),

l as (

select owner, table_name, column_name, tablespace_name, segment_name,

partitioned, securefile, pctversion, retention, chunk

from dba_lobs

),

s as (

select owner, segment_name, sum(bytes) bytes_alloc

from dba_segments

where segment_type in ('LOBSEGMENT','LOB PARTITION')

group by owner, segment_name

),

calc as (

select

l.owner, l.table_name, l.column_name, l.tablespace_name,

l.partitioned, nvl(l.securefile,'NO') securefile,

l.pctversion, l.retention, nvl(l.chunk,0) chunk,

nvl(s.bytes_alloc,0) as bytes_alloc,

(nvl(t.num_rows,0) * nvl(c.avg_col_len,0)) as bytes_data,

case when nvl(s.bytes_alloc,0) > 0

then greatest(0, nvl(s.bytes_alloc,0) - (nvl(t.num_rows,0) * nvl(c.avg_col_len,0)))

else 0 end as bytes_wasted,

case when nvl(s.bytes_alloc,0) > 0

then round(100 * ( greatest(0, nvl(s.bytes_alloc,0) - (nvl(t.num_rows,0) * nvl(c.avg_col_len,0))) ) / nvl(s.bytes_alloc,0), 2)

else null end as pct_reclaim

from l

left join t on (t.owner = l.owner and t.table_name = l.table_name)

left join c on (c.owner = l.owner and c.table_name = l.table_name and c.column_name = l.column_name)

left join s on (s.owner = l.owner and s.segment_name = l.segment_name)

)

select

'<tr class="'||

case when pct_reclaim >= 90 then 'sev-red'

when pct_reclaim >= ${THRESHOLD} then 'sev-amber'

else 'sev-ok' end

||'"><td>'||replace(replace(replace(owner,'&','&amp;'),'<','&lt;'),'>','&gt;')||

'</td><td>'||replace(replace(replace(table_name,'&','&amp;'),'<','&lt;'),'>','&gt;')||

'</td><td>'||replace(replace(replace(column_name,'&','&amp;'),'<','&lt;'),'>','&gt;')||

'</td><td>'||securefile||

'</td><td>'||replace(replace(replace(partitioned,'&','&amp;'),'<','&lt;'),'>','&gt;')||

'</td><td>'||replace(replace(replace(tablespace_name,'&','&amp;'),'<','&lt;'),'>','&gt;')||

'</td><td class="num">'||to_char(round(nvl(chunk,0)/1024,0),'999,990')||

'</td><td class="num">'||nvl(to_char(pctversion),'')||

'</td><td class="num">'||nvl(to_char(retention),'')||

'</td><td class="num">'||to_char(round(bytes_alloc/1024/1024/1024,2),'999,990.00')||

'</td><td class="num">'||to_char(round(bytes_data/1024/1024/1024,2),'999,990.00')||

'</td><td class="num">'||to_char(round(bytes_wasted/1024/1024/1024,2),'999,990.00')||

'</td><td class="num">'||nvl(to_char(pct_reclaim,'990.00'),'')||

'</td><td>'||

case

when pct_reclaim is null or pct_reclaim < ${THRESHOLD} then 'MONITOR'

when securefile = 'YES' then 'SHRINK LOB SEGMENT; REBUILD LOB INDEX; REVIEW RETENTION/PCTVERSION'

else 'MIGRATE TO SECUREFILE (ONLINE if possible) THEN SHRINK; REBUILD LOB INDEX; REVIEW RETENTION/PCTVERSION'

end

||'</td></tr>'

from calc

where bytes_alloc > 0

order by bytes_wasted desc

fetch first 50 rows only;

prompt </tbody>

prompt </table>

prompt </div>

SQL

# -------- Send HTML Email --------

{

echo "From: ${FROM_EMAIL}"

echo "To: ${TO_EMAIL}"

echo "Subject: ${SUBJECT}"

echo "MIME-Version: 1.0"

echo "Content-Type: text/html; charset=UTF-8"

echo

cat <<'HTMLTOP'

<html>

<head>

<meta charset="utf-8" />

<style>

body { font-family: Segoe UI, Arial, sans-serif; font-size: 13px; color: #1f2937; margin: 0; padding: 16px; background: #ffffff; }

.header { background: #0F6CBD; color: #fff; padding: 12px 16px; border-radius: 8px; }

.header h1 { margin: 0 0 6px 0; font-size: 18px; }

.meta { margin-top: 4px; }

.badge { display: inline-block; background: rgba(255,255,255,0.2); padding: 2px 8px; border-radius: 999px; margin-right: 6px; font-size: 12px; }

.section { margin-top: 18px; }

.section h2 { margin: 0; font-size: 16px; color: #0F6CBD; }

.section h3 { margin: 10px 0 0; font-size: 14px; color: #0F6CBD; }

.small { color: #6b7280; font-size: 12px; margin-top: 6px; }

.chip { display: inline-block; padding: 2px 8px; border-radius: 999px; margin: 4px 6px 0 0; font-size: 12px; border: 1px solid #e5e7eb; }

.chip.ok { background: #ecfdf5; color: #065f46; border-color: #a7f3d0; }

.chip.amber { background: #fffbeb; color: #92400e; border-color: #fcd34d; }

.chip.red { background: #fef2f2; color: #991b1b; border-color: #fecaca; }

.table { border-collapse: collapse; width: 100%; margin-top: 8px; table-layout: fixed; }

.table th { text-align: left; background: #f3f4f6; color: #111827; padding: 8px; border-bottom: 1px solid #e5e7eb; }

.table td { padding: 6px 8px; border-bottom: 1px solid #f1f5f9; word-break: break-word; }

.table tr:nth-child(even) td { background: #fbfdff; }

.num { text-align: right; font-variant-numeric: tabular-nums; }

.sev-ok td { background: #ecfdf5; }

.sev-amber td { background: #fffbeb; }

.sev-red td { background: #fef2f2; }

.footer { margin-top: 16px; color: #6b7280; font-size: 12px; }

@media (prefers-color-scheme: dark) {

body { background: #0b0f17; color: #e5e7eb; }

.header { background: #185ABD; }

.table th { background: #111827; color: #e5e7eb; border-color: #374151; }

.table td { border-color: #1f2937; }

.table tr:nth-child(even) td { background: #0f172a; }

}

</style>

</head>

<body>

HTMLTOP

cat "$TMP_REPORT"

cat <<'HTMLBOTTOM'

<div class="footer">

<div>Notes:</div>

<ul>

<li>Tablespace “Used% Max” considers autoextend MAXBYTES.</li>

<li>Table and LOB fragmentation are estimates based on statistics; gather stats for accuracy.</li>

<li>LOB reclaim: SecureFile supports <code>SHRINK SPACE</code>. BasicFile usually requires migration to SecureFile first.</li>

<li>Recommended actions are guidance; validate in non-prod and consider maintenance windows.</li>

</ul>

</div>

</body>

</html>

HTMLBOTTOM

} | "${SENDMAIL_BIN}" -t

echo "Report mailed to: ${TO_EMAIL}"

Efficacy: When (and how) this helps

• Capacity planning: Clear view of true headroom vs. max autoextend, per tablespace
• Reclamation candidates: Quick wins with SHRINK/COALESCE where it matters most
• LOB spotlight: LOBs are silent space consumers—now you know where to SHRINK or MIGRATE to SecureFile
• AWR hygiene: Ensures retention/interval settings match your troubleshooting needs and license posture
• Ops-friendly: Clean HTML, color-coded, low-friction email format; easy to spot red flags

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment