Stale cursors

A stale cursor is a cursor that points to information that is out of date in an Oracle database. When you execute SQL on a cursor, Oracle hides any changes that occur to the database after that point. It reconstructs the state of the database at the time the cursor was created by using before-image information it writes to rollback segments. There is a limited number of rollback segments so Oracle reuses them. A cursor whose rollback-segment information has been overwritten is stale, and if you attempt to fetch data on a stale cursor, the fetch fails.

An application is most likely to encounter a stale cursor if it has a long loop or a loop that might take a long time because it involves user interaction and simultaneously allows updates to the same table the loop accesses. FIND NEXT and PREV statements are also likely candidates for stale cursors.

To avoid stale cursors, you can tune your database environment or adjust your application. For example, you can increase the number and size of rollback segments for the Oracle database. To adjust your application, consider putting the record identifiers for the table that must be updated into a temp table. Standard cursors are somewhat less likely to become stale than lookahead cursors. Although you might lose some performance, using the QUERY-TUNING NO-LOOKAHEAD option might help you avoid stale cursors. See Query tuning for instructions.