DBA SYSAUX Occupants – 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
Space used in the SYSAUX tablespace by component.
All sizes are shown in MB.
The largest occupants are usually the AWR (SM/AWR) and the optimizer statistics history (SM/OPTSTAT). The SM/OPTSTAT row shows the statistics history retention in days and the oldest statistics still available. A lower retention, set with dbms_stats.alter_stats_history_retention, reduces the SYSAUX space used.
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
Report Categories
Related Reports
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 SYSAUX Occupants 12-Oct-2026 061636.xlsx |
| Blitz Report™ XML Import | DBA_SYSAUX_Occupants.xml |
| SQL Source | DBA SYSAUX Occupants.sql |
| Full Report on Enginatics | www.enginatics.com/reports/dba-sysaux-occupants/ |
Case Study & Technical Analysis: DBA SYSAUX Occupants
Executive Summary
The DBA SYSAUX Occupants report shows how the SYSAUX tablespace space is used, broken down by component. Each row is an occupant, such as the Automatic Workload Repository or the optimizer statistics history, with its schema, space used in MB, and its share of the total. Database administrators use it when SYSAUX grows unexpectedly, when planning storage, or when deciding whether to reduce a retention period. The report has no parameters, and its rows are sorted by space used, so the largest consumers appear first.
Business Challenge
- Unexplained SYSAUX growth: SYSAUX holds the auxiliary data of many database features, and its growth is often attributed to the wrong component without a breakdown.
- Retention decisions: Components such as workload repository snapshots and statistics history grow with their retention settings, and the cost of a longer retention needs to be seen in MB.
- Capacity planning: Storage teams need the share each component takes, so they can plan space for SYSAUX and the instance as a whole.
- Relocation options: Some occupants can be moved to another tablespace, and their move procedures need to be identified before a change is made.
The Solution
- Component inventory: Every occupant of SYSAUX is listed with its name, description and owning schema.
- Space and share: Space used is shown in MB, and a percentage column shows each occupant’s share of total SYSAUX usage.
- Statistics history retention: For the optimizer statistics history occupant, the report shows the configured retention in days and the oldest statistics still available.
- Move guidance: The move procedure and its description are shown for each occupant that supports relocation.
Technical Architecture (High Level)
Primary Tables
V$SYSAUX_OCCUPANTS: the dynamic view that lists each SYSAUX occupant with its schema, space usage in kilobytes and move procedure.
Logical Relationships
- The report reads
V$SYSAUX_OCCUPANTSas a single source. No joins are used. - The share of total usage is calculated with a ratio-to-report analytic function over all occupant rows.
- For the statistics history occupant only, the report calls the statistics history retention and availability functions, and converts the availability date for display.
- The result is sorted by space usage in descending order.
Parameters & Filtering
This report has no user parameters. It always returns every occupant of SYSAUX, so filtering is done by reading the sorted output rather than by changing the query.
Performance & Optimization
- Single dynamic view: The report reads one dynamic performance view with no joins, so the query is a single read of the occupant list.
- Analytic share calculation: The percentage column uses a ratio-to-report function, so each occupant’s share is computed in the same pass without a separate total query.
- Conditional function calls: The statistics retention and availability functions are called only for the optimizer statistics history row, so the other rows do not trigger them.
- Sorted at source: Ordering by space usage lets the largest occupants be read first.
FAQ
Q: Which SYSAUX occupants usually take the most space? A: The description notes that the largest occupants are usually the AWR (SM/AWR) and the optimizer statistics history (SM/OPTSTAT), and the report sorts them to the top. Q: How do I reduce the space used by optimizer statistics history? A: The description says a lower retention, set with the DBMS_STATS alter statistics history retention procedure, reduces SYSAUX space used. The SM/OPTSTAT row shows the current retention in days. Q: Why does the report have no parameters? A: It lists every occupant of SYSAUX, so there is no selection to make, and the output is already sorted by space used.
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