Undo Issue, Stats Collection Job ORA$AT_OS_OPT_SY_XXX Is Failing Because Of Lack Of Undo
(Doc ID 2602674.1)
Last updated on NOVEMBER 13, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
Symptoms
On : 12.1.0.2 version, RDBMS
Undo issue, stats collection job ORA$AT_OS_OPT_SY_XXXX is failing because of lack of undo
Failure of our stats collection job due to undo tablespace issues.
Script - Check Current Undo Configuration and Advise Recommended Setup (Doc ID 1579035.1)
Output of the script :
SQL> @undo.sql
Session altered.
- Undo Analysis started -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : -
End Time : -
Current Undo Configuration
--------------------------
Current undo tablespace : UNDOTBS
Current undo tablespace size (datafile size now) : 262967.984375M
Current undo tablespace size (consider autoextend) : 0M
AUTOEXTEND for undo tablespace is : OFF
Current undo retention : 86400
UNDO GUARANTEE is set to : FALSE
Undo Advisor Summary
---------------------------
Finding 1:Undo Tablespace is under pressure. Recommendation 1:Size undo tablespace to 2261 MB
Undo Space Recommendation
-------------------------
Minimum Recommendation : Size undo tablespace to 727253 MB
Rationale : Increase undo tablespace size so that long running queries will not fail
Recommended Undo Tablespace Size : 727253M
Retention Recommendation
------------------------
The best possible retention with current configuration is : 11894 Seconds
The longest running query ran for : 60825 Seconds
The undo retention required to avoid errors is : 60825 Seconds
PL/SQL procedure successfully completed.
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |