DBA SYSAUX Occupants

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Space used in the SYSAUX tablespace by component.

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.
select
vso.occupant_name,
vso.occupant_desc description,
vso.schema_name,
vso.space_usage_kbytes*1024/1000000 space_used,
ratio_to_report(vso.space_usage_kbytes) over ()*100 percentage,
case when vso.occupant_name='SM/OPTSTAT' then dbms_stats.get_stats_history_retention end retention_days,
case when vso.occupant_name='SM/OPTSTAT' then xxen_util.client_time(cast(dbms_stats.get_stats_history_availability as date)) end oldest_available,
vso.move_procedure,
vso.move_procedure_desc move_procedure_description
from
v$sysaux_occupants vso
order by
vso.space_usage_kbytes desc
Download
Related Blitz Reports
TMEIC Purchase Register JA India - RG23C Part I Report - draft - draft HXT Time Entry Detail ECC Receivables, Payment Process, SQL3 ECC Receivables, Payment Process, SQL2 ECC Receivables, Payment Process, SQL1 AP Period Close Exceptions Report- Delete - Not Supported: Reserved For Future Use AP Invoice Aging Report- Not Supported: Reserved For Future Use AR Print Remittances
Blitz Report™