DBA AWR 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 over time from the AWR
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
Date From, Diagnostic Pack enabled, Container Data
Oracle EBS Tables Used
v$parameter, v$database, dba_hist_tbspc_space_usage, ts$
Report Categories
Related Reports
DBA SGA+PGA Memory Configuration, DBA AWR System Metrics Summary, DBA Tablespace Usage, DBA AWR Active Session History, DBA Undo Statistics, DBA AWR Settings, DBA Feature Usage Statistics, DBA AWR SQL Execution Plan History
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 AWR Tablespace Usage 18-Jan-2018 223607.xlsx |
| Blitz Report™ XML Import | DBA_AWR_Tablespace_Usage.xml |
| SQL Source | DBA AWR Tablespace Usage.sql |
| Full Report on Enginatics | www.enginatics.com/reports/dba-awr-tablespace-usage/ |
Executive Summary
The DBA AWR Tablespace Usage report tracks the historical growth of database storage. Unlike real-time checks that only show current usage, this report uses AWR history to show trends. This is essential for capacity planning, allowing DBAs to predict when a tablespace will fill up based on its growth rate over the last month or year.
Business Challenge
- Capacity Planning: “When will we need to buy more disk storage?”
- Budgeting: “How much storage did the ‘Archive’ tablespace consume last year?”
- Anomaly Detection: “Why did the UNDO tablespace suddenly grow by 50GB yesterday?”
Solution
This report displays the used size of tablespaces over time.
Key Features:
- Used Space: The amount of space actually occupied by data.
- Allocated Space: The size of the datafiles.
- Growth Trend: By comparing snapshots, you can calculate the daily growth rate.
Architecture
The report queries DBA_HIST_TBSPC_SPACE_USAGE.
Key Tables:
DBA_HIST_TBSPC_SPACE_USAGE: Historical tablespace usage metrics.TS$: Tablespace metadata.
Impact
- Uptime: Prevents outages caused by tablespaces reaching 100% capacity.
- Cost Control: Helps justify storage purchases and identify wasted space.
- Proactive Management: Allows DBAs to add space during maintenance windows rather than in a panic during business hours.
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