Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sundar.

Asked: May 11, 2020 - 8:56 am UTC

Last updated: May 12, 2020 - 8:39 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

SYSAUX tablespace growing rapidly on one of our critical database. The current interval & retention for AWR reports are of 30min and 7days respectively.

Please find the points that we analyzed to resolve the issue till now as below :
Analysis :
1) Firstly, We analyzed the AWRINFO report and we observed that the below components are consuming most of SYSAUX tablespace.

OCCUPANT_DESC SPACE_USAGE_GBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Automatic Workload Repository 72.5
Server Manageability - Advisor Framework 10.4

2) Secondly, We found the orphaned records, purged them & shrink the table WRH$_ACTIVE_SESSION_HISTORY as well however the space was not releasing to SYSAUX tablespace.

3) Thirdly, We split the table partition of WRH$_ACTIVE_SESSION_HISTORY by setting the parameter "_swrf_test_action = 72" at session level, even after that we haven’t observed any space release.


OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_GB
------------------------- ---------------------------------------- ---------------------------------------- ------------------------- ----------
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_40021 TABLE PARTITION 4.57550049
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_39877 TABLE PARTITION 8.05413818
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_39781 TABLE PARTITION 7.1986084
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_40049 TABLE PARTITION 4.78857422
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_40121 TABLE PARTITION 1.29003906
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_39925 TABLE PARTITION 8.29864502
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_39829 TABLE PARTITION 7.72070313
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_39973 TABLE PARTITION 8.45422363
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_40097 TABLE PARTITION 5.03320313
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_40193 TABLE PARTITION .000061035
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_40145 TABLE PARTITION .1484375
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_40169 TABLE PARTITION .000061035
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3964960440_40073 TABLE PARTITION 4.78448486

Could you please provide your inputs and guide us to resolve the issue.

and Connor said...


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database