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:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.