-- 1. Drop the existing control table (PURGE ensures it doesn't linger in the recycle bin)
DROP TABLE rdsadm.elo_ln_rebuild_driver PURGE;
EXEC DBMS_SCHEDULER.DROP_JOB(job_name => 'MASS_IDX_REBUILD_ELO_LN', force => TRUE);BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'MASS_IDX_REBUILD_ELO_LN',
job_type => 'PLSQL_BLOCK',
job_action => '
DECLARE
v_err_msg VARCHAR2(4000);
v_module VARCHAR2(48) := ''IDX_REBUILD_JOB'';
BEGIN
-- Wait up to 60 seconds for active application transactions (Zero DDL-blocking)
EXECUTE IMMEDIATE ''ALTER SESSION SET DDL_LOCK_TIMEOUT = 60'';
FOR rec IN (
SELECT driver_id, rebuild_ddl, index_name, partition_name, segment_bytes
FROM rdsadm.elo_ln_rebuild_driver
WHERE status IN (''PENDING'', ''FAILED'')
-- Safeguard: Skip segments > 30 GB (32212254720 bytes) to protect 53 GB free space margin
AND segment_bytes < 32212254720
ORDER BY segment_bytes ASC
) LOOP
-- Publish live execution status to v$session
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => v_module,
action_name => ''Rebuilding: '' || substr(rec.index_name, 1, 15)
);
UPDATE rdsadm.elo_ln_rebuild_driver
SET status = ''PROCESSING'', start_time = SYSTIMESTAMP
WHERE driver_id = rec.driver_id;
COMMIT;
BEGIN
EXECUTE IMMEDIATE rec.rebuild_ddl;
UPDATE rdsadm.elo_ln_rebuild_driver
SET status = ''SUCCESS'', end_time = SYSTIMESTAMP
WHERE driver_id = rec.driver_id;
COMMIT;
EXCEPTION WHEN OTHERS THEN
v_err_msg := SUBSTR(SQLERRM, 1, 3900);
UPDATE rdsadm.elo_ln_rebuild_driver
SET status = ''FAILED'', end_time = SYSTIMESTAMP, error_message = v_err_msg
WHERE driver_id = rec.driver_id;
COMMIT;
END;
-- REDO THROTTLE: If segment was > 2 GB (2147483648 bytes), sleep for 60 seconds
-- to allow replica sync and prevent exhausting the 2 TB physical margin
IF rec.segment_bytes > 2147483648 THEN
DBMS_SESSION.SLEEP(60);
END IF;
END LOOP;
-- Clear session info upon completion
DBMS_APPLICATION_INFO.SET_MODULE(null, null);
END;',
start_date => SYSTIMESTAMP,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Resumable online partition rebuild (Numeric Overflow Fixed).'
);
END;
/Here is the complete suite of DBA_SCHEDULER queries to monitor, troubleshoot, and control the MASS_IDX_REBUILD_ELO_LN job from configuration to execution history.
1. Live Execution: Is the job currently running?
This query joins with V$SESSION to show exactly how long the job has been actively running, its CPU usage, and the specific database session it is occupying.
SQLSELECT
r.job_name,
r.session_id,
s.serial#,
r.status,
r.elapsed_time,
r.cpu_used,
s.sql_id,
s.event AS current_wait_event
FROM
dba_scheduler_running_jobs r
LEFT JOIN
v$session s ON r.session_id = s.sid
WHERE
r.job_name = 'MASS_IDX_REBUILD_ELO_LN';
2. Job Configuration: Is the job enabled and configured correctly?
Use this to verify the job exists, check if it is enabled, and review its defined failover or retry parameters.
SQLSELECT
job_name,
job_type,
job_action,
enabled,
state,
run_count,
failure_count,
last_start_date,
next_run_date,
auto_drop
FROM
dba_scheduler_jobs
WHERE
job_name = 'MASS_IDX_REBUILD_ELO_LN';
3. Execution History: Did previous runs succeed or fail?
This view provides the granular history of every time the job started and stopped, including the exact ORA error codes and text if the PL/SQL block crashed.
SQLSELECT
log_id,
job_name,
status,
req_start_date,
actual_start_date,
run_duration,
error# AS error_code,
additional_info AS error_message
FROM
dba_scheduler_job_run_details
WHERE
job_name = 'MASS_IDX_REBUILD_ELO_LN'
ORDER BY
actual_start_date DESC
FETCH FIRST 20 ROWS ONLY;
4. High-Level Event Log: What state changes occurred?
If a job was manually stopped, dropped, or broken by the system, it will be recorded here rather than in the run details.
SQLSELECT
log_date,
job_name,
operation,
status,
additional_info
FROM
dba_scheduler_job_log
WHERE
job_name = 'MASS_IDX_REBUILD_ELO_LN'
ORDER BY
log_date DESC
FETCH FIRST 20 ROWS ONLY;
5. Operational Commands: Stop or Drop the Job
If archive generation spikes or you need to pause execution, use these commands. Because the script architecture relies on the driver table, stopping the job is completely safe; it will resume from the exact stopping point upon restart.SQL-- Gracefully stop the currently running job
EXEC DBMS_SCHEDULER.STOP_JOB(job_name => 'MASS_IDX_REBUILD_ELO_LN', force => FALSE);
-- Force kill the job immediately (if it is hung on a lock)
EXEC DBMS_SCHEDULER.STOP_JOB(job_name => 'MASS_IDX_REBUILD_ELO_LN', force => TRUE);
-- Drop the job entirely
EXEC DBMS_SCHEDULER.DROP_JOB(job_name => 'MASS_IDX_REBUILD_ELO_LN', force => TRUE);
==============
SELECT
df.tablespace_name,
ROUND(df.total_bytes / 1024 / 1024 / 1024, 2) AS allocated_gb,
ROUND((df.total_bytes - fs.free_bytes) / 1024 / 1024 / 1024, 2) AS used_gb,
ROUND(fs.free_bytes / 1024 / 1024 / 1024, 2) AS free_gb,
ROUND(((df.total_bytes - fs.free_bytes) / df.total_bytes) * 100, 2) AS pct_used
FROM
(SELECT tablespace_name, SUM(bytes) AS total_bytes
FROM dba_data_files
GROUP BY tablespace_name) df
JOIN
(SELECT tablespace_name, SUM(bytes) AS free_bytes
FROM dba_free_space
GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
WHERE df.tablespace_name = '';
SELECT * FROM (
SELECT tablespace_name, status
FROM rdsadm.elo_ln_rebuild_driver
)
PIVOT (
COUNT(status)
FOR status IN (
'SUCCESS' AS successful_rebuilds,
'FAILED' AS failed_rebuilds,
'PROCESSING' AS currently_running,
'PENDING' AS awaiting_execution
)
);
SELECT owner, object_type, count(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type;
SELECT owner, index_name, partition_name, status
FROM dba_ind_partitions
WHERE status = 'UNUSABLE'
UNION ALL
SELECT owner, index_name, NULL, status
FROM dba_indexes
WHERE status = 'UNUSABLE';
===================================
SELECT
f.file_name,
f.file_id,
ROUND(f.bytes / 1024 / 1024 / 1024, 2) AS allocated_gb,
ROUND(NVL(hwm.max_bytes, 0) / 1024 / 1024 / 1024, 2) AS hwm_gb,
ROUND((f.bytes - NVL(hwm.max_bytes, 0)) / 1024 / 1024 / 1024, 2) AS reclaimable_gb,
-- Dynamically generate the resize statement, adding a 2GB safety buffer above the HWM
'ALTER DATABASE DATAFILE ''' || f.file_name || ''' RESIZE ' ||
CEIL((NVL(hwm.max_bytes, 0) / 1024 / 1024) + 2048) || 'M;' AS generated_resize_stmt
FROM
dba_data_files f
LEFT JOIN
(SELECT file_id, MAX(block_id + blocks - 1) * 8192 AS max_bytes
FROM dba_extents
GROUP BY file_id) hwm
ON f.file_id = hwm.file_id
WHERE f.tablespace_name = ''
ORDER BY reclaimable_gb DESC;
=====================================
SELECT
s.owner,
s.segment_name AS index_name,
s.partition_name,
s.segment_type,
s.tablespace_name,
ROUND(s.bytes / 1024 / 1024 / 1024, 2) AS size_gb,
-- Estimate 1.2x overhead for sorting and merging during an ONLINE rebuild
ROUND((s.bytes / 1024 / 1024 / 1024) * 1.2, 2) AS estimated_rebuild_space_gb,
-- Dynamically generate the correct rebuild statement based on partition type
CASE
WHEN s.segment_type = 'INDEX' THEN
'ALTER INDEX ' || s.owner || '.' || s.segment_name || ' REBUILD ONLINE;'
WHEN s.segment_type = 'INDEX PARTITION' THEN
'ALTER INDEX ' || s.owner || '.' || s.segment_name || ' REBUILD PARTITION ' || s.partition_name || ' ONLINE;'
WHEN s.segment_type = 'INDEX SUBPARTITION' THEN
'ALTER INDEX ' || s.owner || '.' || s.segment_name || ' REBUILD SUBPARTITION ' || s.partition_name || ' ONLINE;'
END AS generated_rebuild_stmt
FROM
dba_segments s
WHERE
s.segment_type LIKE 'INDEX%'
-- Uncomment and replace with your specific tablespace name:
-- AND s.tablespace_name = 'YOUR_INDEX_TABLESPACE'
AND s.owner NOT IN ('SYS', 'SYSTEM', 'XDB', 'AUDSYS', 'DBSNMP', 'APPQOSSYS', 'OJVMSYS')
ORDER BY
s.bytes DESC
FETCH FIRST 100 ROWS ONLY;
Subject: Incident Review & Telemetry Remediation: Sept 9 RDS Replica Outage
Executive Summary
The September 9 database outage was caused by an RDS read replica disconnect, which forced the primary database to accumulate over 800 GB of archive logs until its physical storage was exhausted. While a generic primary storage alert from August 29 was unacknowledged, post-incident analysis confirms that relying on primary storage capacity or tablespace metrics to monitor replica health is an architectural anti-pattern. We are deploying AWS-validated, decoupled telemetry to replace manual inference with deterministic alerting.
Key Findings: Why Generic Storage Metrics Cannot Monitor Replica Health
- Primary
FreeStorageSpaceis a Trailing Casualty, not a Root Cause: When an RDS replica disconnects, Oracle architecture dictates the primary must retain all archive logs. The primary storage alert only triggers after the drive fills up with these retained logs. By the time this capacity threshold is breached, the replication tier has already been compromised for a significant duration. - Diagnostic Ambiguity Inflates MTTR: Operating a 64 TB database at 98% capacity leaves a rigid 2 TB margin. Generating 1.5 TB of archive data is a standard, expected workload. Expecting engineers to manually parse generic capacity alerts—which routinely flag benign data loads or temp segment growth—to deduce a hidden replication failure introduces severe diagnostic latency. Incident management must rely on deterministic telemetry, not manual human inference.
- Tablespace Monitoring is Architecturally Irrelevant Here: Tablespace metrics monitor the logical capacity inside the database (application data/indexes). Archive logs consume physical storage entirely outside the database. A database can have 99% free tablespace and still suffer an immediate outage if the physical archive destination fills up. Monitoring tablespaces to catch a replica failure is equivalent to checking a car's tire pressure to diagnose a broken transmission.
Remediation: AWS-Validated Architecture
To eliminate reliance on generalized capacity metrics and permanently close this visibility gap, we are actively deploying the following targeted alerting framework:
- Decoupled Infrastructure Alarms: Implementing dedicated CloudWatch alarms for
FreeStorageSpaceon both the primary database and the read replica independently. - Deterministic RDS Event Notifications: Subscribing to distinct event categories to trigger immediate, real-time paging specifically for "Read Replica status changes" and "Low Storage" events.
- Updated Operational Procedures (SOP): Effective immediately, any generic primary storage alert automatically mandates a cross-validated health check of read replica synchronization status and archive generation rates.
By establishing independent monitoring for the replication layer, we ensure the system alerts immediately on the actual point of failure, drastically reducing Mean Time to Resolution (MTTR).
Executive Summary To address the specific question of whether tablespace monitoring would have provided an early warning or prevented the archive failure, the technical answer is definitively no. Suggesting that tablespace alerts would have caught this issue indicates a fundamental misunderstanding of Oracle database architecture. Tablespaces and archive logs occupy entirely distinct storage domains.
Technical Analysis: Logical Data vs. Physical Recovery Storage It is vital for the engineering teams to understand why tablespace telemetry is completely blind to read replica and archive log failures:
Distinct Storage Domains: Tablespace metrics monitor the internal logical capacity of the database—meaning the space consumed by application data, tables, and indexes inside the datafiles. Conversely, archive logs are physical files generated continuously by the database engine to record transactions. They are stored outside the tablespaces, typically at the file system level or within the Fast Recovery Area (FRA).
Zero Correlation: The accumulation of 800+ GB of archive logs due to the replica disconnect rapidly consumed the underlying physical RDS storage, but it had zero impact on tablespace utilization. The tablespace metrics never changed during this event. A database can have 99% free space in all of its tablespaces and still suffer an immediate catastrophic outage if the external archive destination fills up.
The Wrong Tool for the Job: Monitoring tablespaces to catch a replica synchronization failure is equivalent to checking a car's tire pressure to diagnose a broken transmission. Even if we had real-time, minute-by-minute tablespace monitoring actively scrutinized by the team on September 9, it would have shown the database as completely healthy right up until the exact moment the physical storage filled with archive logs and the system went down.
Conclusion
The assertion that tablespace monitoring would have mitigated this outage is technically invalid. It reinforces our core incident finding: relying on generic capacity metrics—whether primary FreeStorageSpace or internal tablespace utilization—is an ineffective defense against replication failures.
The only technically sound method to prevent this specific failure scenario is through dedicated replica state monitoring and archive generation telemetry, which are exactly the AWS-validated alerting mechanisms we are putting in place.
Operational Noise and Time-to-Resolution:
In a 64 TB tier-1 environment, requiring engineers to investigate every FreeStorageSpace alert across decoupled systems is fragile and unscalable. These alerts are routinely triggered by benign database activities—such as high-volume data loads, index rebuilds, or temporary segment consumption—but can simultaneously mask a critical downstream replication failure. Forcing incident responders to manually parse generic capacity metrics to uncover a hidden replication issue introduces severe diagnostic latency. Enterprise incident management must rely on automated, deterministic telemetry to immediately distinguish routine storage fluctuations from systemic failures, ensuring a rapid Mean Time to Resolution (MTTR).
Hi Team,
I have completed the capacity assessment for the tablespaces currently exceeding 75% utilization.
Based on the current utilization levels, the estimated additional storage requirements are:
To achieve 75% utilization: Approximately 7 TB of additional allocation.
To achieve 90% utilization: Approximately 1,105 GB of additional allocation.
Given our current storage constraints, achieving 75% across all identified tablespaces would require substantial additional capacity.
However, the planned archive tablespace space reclamation may provide sufficient capacity to accommodate the estimated 1,105 GB requirement, subject to confirmation that the reclaimed space is physically available on the appropriate storage volumes.
My recommendation is to prioritize the high-utilization tablespaces and evaluate the 90% sizing option as an interim measure, while continuing to assess space reclamation and optimization opportunities to achieve the longer-term 75% target.
Please note that sizing tablespaces to exactly 90% would leave limited headroom before the existing alert threshold is reached. We should account for anticipated growth and operational reserves before proceeding.
Please let me know your preferred approach so we can finalize the capacity plan and coordinate the implementation.
Best regards,
[Your Name]
/*
ONE-QUERY 75% CAPACITY / DONOR / STORAGE-LOCATION PLAN
Oracle 19c on Amazon RDS. Run as RDSADM on the primary.
READ ONLY: no DDL, no data movement, no scheduler objects.
Scope: original 32 targets plus the six application donor candidates.
SYSTEM is costed separately as a target, never used as a donor.
UNDO, TEMP, audit and RDS-managed tablespaces are not donors.
Assumptions:
- Target and donor utilization ceilings: 75% of allocated file bytes.
- 1,024 GiB is the usable growth allowance AFTER operating reserves.
It is a supplied budget, NOT measured filesystem free space.
- Donor floor = greater of capacity needed to remain <=75% and the
sum of file extent-HWM floors, each with a 1-GiB planning margin.
- Missing extent-HWM information receives NO shrink credit.
- Unknown or mixed storage locations are flagged, not pooled.
- Per-location shortages are added BEFORE comparing with the budget.
Surplus on one RDS volume does not fund growth on another.
- File MAXBYTES is a configured autoextension ceiling, not proof of
manual resize eligibility. Above-ceiling growth is flagged for review.
- HWM floors are estimates, not guaranteed resize minima. Oracle file
metadata, concurrent allocations and storage availability still matter.
- AFTER_ALLOC_EST_GIB is a tablespace TOTAL, not a per-file resize command.
- TAIL_OBJECTS lists the last allocated segment extent in each donor file;
this is not a complete reorganization work list.
*/
WITH
params AS
(
SELECT 0.75 AS target_ratio, 0.75 AS donor_ratio,
1024 * POWER(1024,3) AS budget_bytes,
1024 * POWER(1024,2) AS hwm_margin_bytes,
POWER(1024,2) AS mib
FROM dual
),
scope AS
(
SELECT 'TARGET' AS kind, column_value AS tablespace_name
FROM TABLE(sys.odcivarchar2list(
))
UNION ALL
SELECT 'DONOR', column_value
FROM TABLE(sys.odcivarchar2list(
))
),
free_by_file AS
(
SELECT f.file_id, SUM(f.bytes) AS free_bytes
FROM dba_free_space f
JOIN scope s ON s.tablespace_name = f.tablespace_name
GROUP BY f.file_id
),
extent_hwm AS
(
/* Inspect allocated extent boundaries ONLY for the six donors. */
SELECT e.file_id, MAX(e.block_id + e.blocks) AS end_block,
MAX(e.owner || '.' || e.segment_name ||
CASE WHEN e.partition_name IS NOT NULL
THEN ' [' || e.partition_name || ']' END ||
' (' || e.segment_type || ')')
KEEP (DENSE_RANK LAST ORDER BY e.block_id + e.blocks)
AS tail_object
FROM dba_extents e
JOIN scope s ON s.tablespace_name = e.tablespace_name
AND s.kind = 'DONOR'
GROUP BY e.file_id
),
file_data AS
(
SELECT s.kind, s.tablespace_name, d.file_id, d.file_name,
d.bytes AS allocated_bytes,
d.bytes - NVL(f.free_bytes,0) AS used_bytes,
GREATEST(d.bytes,NVL(d.maxbytes,0)) AS configured_max_bytes,
CASE WHEN REGEXP_LIKE(d.file_name,'^/rdsdbdata[0-9]*/')
THEN REGEXP_SUBSTR(d.file_name,'^/[^/]+')
ELSE 'UNMAPPED' END AS storage_location,
CASE
WHEN t.tablespace_name IS NULL THEN 'NAME NOT FOUND'
WHEN t.contents <> 'PERMANENT' THEN 'NOT PERMANENT'
WHEN t.status <> 'ONLINE' THEN 'TABLESPACE NOT ONLINE'
WHEN t.extent_management <> 'LOCAL' THEN 'NOT LOCALLY MANAGED'
WHEN NVL(d.bytes,0) <= 0 THEN 'NO DATAFILE SIZE'
WHEN NVL(d.status,'?') <> 'AVAILABLE'
OR NVL(d.online_status,'?') NOT IN ('ONLINE','SYSTEM')
THEN 'CHECK FILE STATUS'
WHEN NVL(f.free_bytes,0) > d.bytes THEN 'CHECK SPACE VALUES'
WHEN h.end_block * t.block_size > d.bytes
THEN 'CHECK CHANGING FILE SIZE'
ELSE 'OK'
END AS file_check,
CASE WHEN s.kind = 'DONOR' AND h.end_block IS NOT NULL THEN
LEAST(d.bytes,
CEIL((GREATEST(h.end_block * t.block_size,
d.bytes - NVL(d.user_bytes,0))
+ p.hwm_margin_bytes) / p.mib) * p.mib)
ELSE d.bytes
END AS hwm_keep_bytes,
CASE WHEN s.kind = 'DONOR' THEN
'FILE ' || TO_CHAR(d.file_id) || ': ' ||
NVL(h.tail_object,'NO EXTENT HWM - NO SHRINK CREDIT')
END AS tail_object
FROM scope s
LEFT JOIN dba_tablespaces t ON t.tablespace_name = s.tablespace_name
LEFT JOIN dba_data_files d ON d.tablespace_name = s.tablespace_name
LEFT JOIN free_by_file f ON f.file_id = d.file_id
LEFT JOIN extent_hwm h ON h.file_id = d.file_id
CROSS JOIN params p
),
ts_data AS
(
SELECT kind, tablespace_name, COUNT(file_id) AS file_count,
CASE WHEN COUNT(DISTINCT storage_location) = 1
THEN MIN(storage_location) ELSE 'MULTI_VOLUME' END
AS storage_location,
SUM(allocated_bytes) AS allocated_bytes,
SUM(used_bytes) AS used_bytes,
SUM(configured_max_bytes) AS configured_max_bytes,
SUM(hwm_keep_bytes) AS hwm_keep_bytes,
NVL(MAX(CASE WHEN file_check <> 'OK' THEN file_check END),'OK')
AS data_check,
LISTAGG(tail_object, ' | ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT)
WITHIN GROUP (ORDER BY file_id) AS tail_objects
FROM file_data
GROUP BY kind, tablespace_name
),
sizing AS
(
SELECT t.*,
CASE WHEN data_check = 'OK' AND kind = 'TARGET' THEN
GREATEST(0,CEIL(used_bytes / p.target_ratio / p.mib)
* p.mib - allocated_bytes)
ELSE 0 END AS grow_bytes,
CASE WHEN data_check = 'OK' AND kind = 'DONOR' THEN
GREATEST(0,allocated_bytes -
CEIL(used_bytes / p.donor_ratio / p.mib) * p.mib)
ELSE 0 END AS donor_policy_cap_bytes,
CASE WHEN data_check = 'OK' AND kind = 'DONOR'
AND storage_location NOT IN ('UNMAPPED','MULTI_VOLUME') THEN
GREATEST(0,allocated_bytes - GREATEST(hwm_keep_bytes,
CEIL(used_bytes / p.donor_ratio / p.mib) * p.mib))
ELSE 0 END AS release_bytes,
CASE WHEN data_check <> 'OK'
OR storage_location IN ('UNMAPPED','MULTI_VOLUME')
THEN 1 ELSE 0 END AS plan_issue
FROM ts_data t
CROSS JOIN params p
),
plan AS
(
SELECT s.*,
CASE WHEN plan_issue = 0
THEN allocated_bytes + grow_bytes - release_bytes END
AS after_alloc_bytes,
CASE WHEN kind = 'TARGET' AND grow_bytes > 0
AND allocated_bytes + grow_bytes > configured_max_bytes
THEN 1 ELSE 0 END AS maxsize_review
FROM sizing s
),
volumes AS
(
SELECT storage_location,
SUM(grow_bytes) AS grow_bytes,
SUM(release_bytes) AS release_bytes,
GREATEST(0,SUM(grow_bytes)-SUM(release_bytes)) AS extra_bytes,
SUM(plan_issue) AS issues,
SUM(maxsize_review) AS maxsize_reviews
FROM plan
GROUP BY storage_location
),
overall AS
(
SELECT SUM(grow_bytes) AS grow_bytes,
SUM(release_bytes) AS release_bytes,
SUM(extra_bytes) AS extra_bytes,
SUM(issues) AS issues,
SUM(maxsize_reviews) AS maxsize_reviews
FROM volumes
),
report AS
(
SELECT 0 AS row_order, 'OVERALL' AS row_type,
'PER-VOLUME NETTING' AS storage_location,
'32 TARGETS / 6 DONORS' AS tablespace_name,
CASE
WHEN o.issues > 0 THEN 'HOLD - SEE DATA/LOCATION FLAGS'
WHEN o.extra_bytes > p.budget_bytes
THEN 'SELECTED DONORS INSUFFICIENT - REORG / OTHER CAPACITY'
WHEN o.maxsize_reviews > 0
THEN 'BUDGET MODEL FITS - REVIEW GROWTH SETTINGS'
ELSE 'BUDGET MODEL FITS - DONOR RESIZE FIRST'
END AS next_action,
CAST(NULL AS NUMBER) AS allocated_gib,
CAST(NULL AS NUMBER) AS used_gib,
CAST(NULL AS NUMBER) AS used_pct,
CAST(NULL AS NUMBER) AS after_alloc_est_gib,
CAST(NULL AS NUMBER) AS after_used_est_pct,
CASE WHEN o.issues = 0 THEN
ROUND(o.grow_bytes/POWER(1024,3),2) END AS grow_needed_gib,
CASE WHEN o.issues = 0 THEN
ROUND(o.release_bytes/POWER(1024,3),2) END AS donor_est_gib,
CASE WHEN o.issues = 0 THEN
ROUND(o.extra_bytes/POWER(1024,3),2) END AS extra_needed_gib,
p.budget_bytes/POWER(1024,3) AS budget_gib,
CASE WHEN o.issues = 0 THEN ROUND(
GREATEST(0,o.extra_bytes-p.budget_bytes)/POWER(1024,3),2)
END AS over_budget_gib,
CAST(NULL AS NUMBER) AS donor_policy_cap_gib,
CAST(NULL AS NUMBER) AS file_count,
CAST(NULL AS VARCHAR2(4000)) AS tail_objects,
CASE WHEN o.issues > 0 THEN 'INCOMPLETE'
ELSE 'ESTIMATE - NOT EXECUTION APPROVAL' END AS data_check
FROM overall o CROSS JOIN params p
UNION ALL
SELECT 1, 'VOLUME', v.storage_location, 'VOLUME SUBTOTAL',
CASE WHEN v.issues > 0 THEN 'HOLD - SEE DETAIL FLAGS'
WHEN v.grow_bytes = 0 THEN 'SURPLUS NOT CREDITED TO OTHER VOLUMES'
WHEN v.extra_bytes = 0 THEN 'DONOR ESTIMATE COVERS TARGET GROWTH'
ELSE 'EXTRA CAPACITY REQUIRED ON THIS VOLUME' END,
NULL,NULL,NULL,NULL,NULL,
CASE WHEN v.issues = 0 THEN ROUND(v.grow_bytes/POWER(1024,3),2) END,
CASE WHEN v.issues = 0 THEN ROUND(v.release_bytes/POWER(1024,3),2) END,
CASE WHEN v.issues = 0 THEN ROUND(v.extra_bytes/POWER(1024,3),2) END,
NULL,NULL,NULL,NULL,NULL,
CASE WHEN v.issues > 0 THEN 'INCOMPLETE'
ELSE 'PATH-BASED STORAGE GROUP' END
FROM volumes v
UNION ALL
SELECT CASE WHEN t.kind = 'DONOR' THEN 2
WHEN t.tablespace_name = 'SYSTEM' THEN 4 ELSE 3 END,
CASE WHEN t.tablespace_name = 'SYSTEM' THEN 'DATABASE TARGET'
ELSE t.kind END,
t.storage_location, t.tablespace_name,
CASE
WHEN t.plan_issue > 0 THEN 'HOLD - SEE DATA CHECK / LOCATION'
WHEN t.kind = 'DONOR' AND t.release_bytes > 0
THEN 'DONOR RESIZE CANDIDATE - NO OBJECT MOVE ESTIMATED'
WHEN t.kind = 'DONOR' AND t.donor_policy_cap_bytes > 0
THEN 'NO TAIL CREDIT - REVIEW TAIL_OBJECTS'
WHEN t.kind = 'DONOR' THEN 'KEEP SIZE - PRESERVE 75% HEADROOM'
WHEN t.tablespace_name = 'SYSTEM'
THEN 'SYSTEM TARGET - SEPARATE CHANGE APPROVAL'
WHEN t.maxsize_review = 1 THEN 'GROWTH ABOVE CONFIGURED MAXBYTES - REVIEW'
WHEN t.grow_bytes > 0 THEN 'GROW AFTER SPACE IS AVAILABLE ON THIS VOLUME'
ELSE 'KEEP CURRENT SIZE'
END,
ROUND(t.allocated_bytes/POWER(1024,3),2),
CASE WHEN t.data_check = 'OK' THEN ROUND(t.used_bytes/POWER(1024,3),2) END,
CASE WHEN t.data_check = 'OK' THEN
ROUND(100*t.used_bytes/NULLIF(t.allocated_bytes,0),2) END,
ROUND(t.after_alloc_bytes/POWER(1024,3),2),
ROUND(100*t.used_bytes/NULLIF(t.after_alloc_bytes,0),2),
CASE WHEN t.data_check = 'OK' THEN ROUND(t.grow_bytes/POWER(1024,3),2) END,
CASE WHEN t.plan_issue = 0 THEN ROUND(t.release_bytes/POWER(1024,3),2) END,
NULL,NULL,NULL,
CASE WHEN t.kind = 'DONOR' AND t.data_check = 'OK' THEN
ROUND(t.donor_policy_cap_bytes/POWER(1024,3),2) END,
t.file_count,t.tail_objects,
CASE WHEN t.data_check <> 'OK' THEN t.data_check
WHEN t.storage_location IN ('UNMAPPED','MULTI_VOLUME')
THEN 'LOCATION NEEDS SEPARATE MAPPING'
WHEN t.maxsize_review = 1 THEN 'CONFIGURED MAXBYTES REVIEW'
ELSE 'ESTIMATE - NOT EXECUTION APPROVAL' END
FROM plan t
)
SELECT /*+ NO_PARALLEL */
row_type, storage_location, tablespace_name, next_action,
allocated_gib, used_gib, used_pct,
after_alloc_est_gib, after_used_est_pct,
grow_needed_gib, donor_est_gib, extra_needed_gib,
budget_gib, over_budget_gib, donor_policy_cap_gib,
file_count, tail_objects, data_check
FROM report
ORDER BY row_order,
CASE WHEN row_order = 1 THEN storage_location END,
CASE WHEN row_order = 2 THEN donor_est_gib END DESC NULLS LAST,
CASE WHEN row_order IN (3,4) THEN grow_needed_gib END DESC NULLS LAST,
tablespace_name;
====================
WITH
file_hwm AS
(
SELECT e.file_id,
MAX(e.block_id + e.blocks - 1) AS hwm_block
FROM dba_extents e
GROUP BY e.file_id
),
files AS
(
SELECT d.tablespace_name,
d.file_id,
d.file_name,
d.bytes,
d.blocks,
t.block_size,
NVL(h.hwm_block, 0) AS hwm_block
FROM dba_data_files d
JOIN dba_tablespaces t
ON t.tablespace_name = d.tablespace_name
LEFT JOIN file_hwm h
ON h.file_id = d.file_id
WHERE t.contents = 'PERMANENT'
),
estimates AS
(
SELECT tablespace_name,
file_id,
file_name,
bytes AS allocated_bytes,
/* Extent HWM plus conservative 64 MiB headroom. */
LEAST(
bytes,
GREATEST(
64 * POWER(1024, 2),
(hwm_block + 1) * block_size
+ 64 * POWER(1024, 2)
)
) AS estimated_min_bytes
FROM files
)
SELECT
tablespace_name,
file_id,
ROUND(allocated_bytes / POWER(1024,3),2)
AS allocated_gib,
ROUND(estimated_min_bytes / POWER(1024,3),2)
AS estimated_min_gib,
ROUND(
GREATEST(0, allocated_bytes - estimated_min_bytes)
/ POWER(1024,3), 2
) AS theoretical_shrink_gib,
file_name
FROM estimates
ORDER BY theoretical_shrink_gib DESC;
