TDA displaying an error "Snap shot is too old - Toad cannot retrieve records or run query". (4258567)

TDA displaying an error "Snap shot is too old - Toad cannot retrieve records or run query".

TDA displaying an error "Snap shot is too old - Toad cannot retrieve records or run query". This happent when attempting to run long running queries, then wait for about 3-4 hours.  Sometimes the query won't run and I will get the error "Snapshot is too old".  Or  After the query runs, it brings ONLY 500 records (i.e. the default). If I did not extract all the data at that time and waited for few hours and tried to extract the data later (Such as the next morning), I get the same error. I need to know how TDA/oracle performs behind the scenes once a query is executed. Does oracle take a snap shot of the affected tables when the query starts executing or does this happen at the end of the execution time. If I know that, I can construct my queries accordingly. Let me demonstrate this in an example: If I ran a query with a condition on Unit_Status = "Open". I run my query at 8:00AM. My query takes 2 hours to run. I had 2,000 units that are open at 8:00 AM (When I started my query). However, at 9:00AM, there were 9 units that got closed (i.e... Re: Regarding |SR Number:2018117| - TDA 3.0, sent to report shut down toad

The result should be the same when runnging in SQL Plus for long query.   This is an issue on the oracle end. 
Information on this issue and how to address it on oracle db end is in the link below:
http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

Content from link above:

The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 10:30 AM, Oracle ensures that all rows are as they appeared at 10:30 AM, even if the query runs until noon!

Oracles does this by reading the "before image" of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear.

From the docs we see that the ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter:

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.

Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

You can get an ORA-01555 error with a too-small undo_retention, even with a large undo tables. However, you can set a super-high value for undo_retention and still get an ORA-01555 error. Also see these important notes on commit frequency and the ORA-01555 error

The ORA-01555 snapshot too old error can be addressed by several remedies:

1. Re-schedule long-running queries when the system has less DML load.

2. Increasing the size of your rollback segment (undo) size. The ORA-01555 snapshot too old also relates to your setting for automatic undo retention.

3. Don't fetch between commits.

Avoiding the ORA-01555 error

Steve Adams has good notes on avoiding the ora-1555 snapshot too old error:

• Do not run discrete transactions while sensitive queries or transactions are running, unless you are confident that the data sets required are mutually exclusive.

• Schedule long running queries and transactions out of hours, so that the consistent gets will not need to rollback changes made since the snapshot SCN. This also reduces the work done by the server, and thus improves performance.

• Code long running processes as a series of restartable steps.

• Shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce risk of consistent get rollback failure due to extent deallocation.

• Use a large optimal value on all rollback segments, to delay extent reuse.

• Don't fetch across commits. That is, don't fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.

• Use a large database block size to maximize the number of slots in the rollback segment transaction tables, and thus delay slot reuse.

• Commit less often in tasks that will run at the same time as the sensitive query, particularly in PL/SQL procedures, to reduce transaction slot reuse.

• If necessary, add extra rollback segments (undo logs) to make more transaction slots available.

 

If you still need further assistance with this, please contact Oracle support for further assistance.