DBA Tablespace Usage – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Database Administration · all Database Administration reports
Overview
Tablespace usage including currently active undo and temp tablepace usage in Megabytes
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
None
Oracle EBS Tables Used
v$sort_segment, dba_tablespaces, v$parameter, dba_tablespace_usage_metrics, dba_data_files, dba_temp_files, dba_undo_extents
Report Categories
Related Reports
DBA Data Files, DBA Sessions, DBA SGA+PGA Memory Configuration, Blitz Report Web Service Sessions, DBA AWR Tablespace Usage, DBA SGA Active Session History, DBA AWR Active Session History, DBA Undo Statistics, DBA SGA Blocking Session Summary
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | DBA Tablespace Usage 09-Mar-2021 101632.xlsx |
| Blitz Report™ XML Import | DBA_Tablespace_Usage.xml |
| SQL Source | DBA Tablespace Usage.sql |
| Full Report on Enginatics | www.enginatics.com/reports/dba-tablespace-usage/ |
Case Study & Technical Analysis
Abstract
The DBA Tablespace Usage report is the primary instrument for storage capacity planning and monitoring. It provides a unified view of space utilization across all types of tablespaces: Permanent (Data/Index), Temporary (Sort/Hash), and Undo (Rollback). By calculating the “Used” vs. “Allocated” vs. “Max Size” (autoextend), it gives a true picture of remaining capacity.
Technical Analysis
Core Logic
- Permanent Tablespaces: usage is calculated based on extent allocation in
DBA_DATA_FILES. - Temp Tablespaces: Usage is dynamic, based on
V$SORT_SEGMENTorDBA_TEMP_FILES. - Undo Tablespaces: Usage is based on active vs. expired extents in
DBA_UNDO_EXTENTS.
Key Views
DBA_TABLESPACE_USAGE_METRICS: A convenient view that pre-calculates usage percentages, accounting for auto-extensibility.DBA_DATA_FILES/DBA_TEMP_FILES: Physical file definitions.
Operational Use Cases
- Alerting: “Alert me when the
APPS_TS_TX_DATAtablespace is 90% full.” - Cleanup: Identifying tablespaces that are unexpectedly filling up due to unpurged interface tables.
- Undo Sizing: Monitoring if the Undo tablespace is large enough to support the
UNDO_RETENTIONperiod without “Snapshot too old” errors.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH