Database Cursor Remains Opened When PDQ Has More Than 30 Records. It Can Cause ORA-01555 Error. (Doc ID 2257979.1)

Last updated on FEBRUARY 06, 2019

Applies to:

Symptoms

WHAT IS THE DESIRED BEHAVIOR?
=============================
When PDQ is executed, the corresponding cursor should be closed once it is finished.

WHAT IS THE ACTUAL OR UNEXPECTED BEHAVIOR?
==========================================
After PDQ is executed, the corresponding cursor still remains opened when the total number of PDQ for the view is 31 or more. The same cursor is used when any PDQ is executed on the same view later.

It remains opened as long as the web client session continues, no matter if other views are displayed between the first and the second (or later) PDQ. Since the same cursor is used to fetch records long after it was first opened, "ORA-01555: snapshot too old" can be reported when the cursor is opened longer than the value of Oracle initialization parameter UNDO_RETENTION. Here is a simplified example.

1) Set UNDO_RETENTION parameter to 5400 (90 minutes)
2) 09:00 AM Using a web client, display Contact List View that has 31 or more PDQ records.
3) 09:00 AM Execute one of PDQ.
4) Navigate to other views and perform necessary operations. (Or stay on the Contact List View as long as the web client session continues.)
5) 11:00 AM Display Contact List View and execute any PDQ there.

The cursor opened at step 3) still remains opened and used at step 5). And the time difference between 3) and 5) is approximately 7200 seconds (2 hours) to exceed the value on UNDO_RETENTION parameter. Depending on other database activities, ORA-01555 may occur at step 5). Even if it doesn't hit ORA-01555 there, it may occur later as long as PDQ on the Contact List View is executed in the same web client session.

To see the SQL statements and the cursor behavior, set the following event log level for the target component.
ObjMgrSqlCursorLog=5
ObjMgrSqlLog=4

Cause

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