Snapshot too old error

oracle teradata informatica
user1601052 picture user1601052 · Aug 15, 2012 · Viewed 17.7k times · Source

I am getting 'snapshot too old error' frequently while i am running my workflow when it runs for more than 5 hrs.My source is oracle and target is Teradata. Please help to solve this issue.Thanks in advance

Answer

Codo picture Codo · Aug 15, 2012

The snapshot too old error is more or less directly related to the running time of your queries (often a cursor of a FOR loop). So the best solution is to optimize your queries so they run faster.

As a short term solution you can try to increase the size of the UNDO log.

Update:

The UNDO log stores the previous version of a record before it's updated. It is used to rollback transactions and to retrieve older version of a record for consistent data snapshots for long running queries.

You'll probably need to dive into Oracle DB administration if you want to solve it via increasing the UNDO log. Basically you do (as SYSDBA):

 ALTER SYSTEM SET UNDO_RETENTION = 21600;

21600 is 6 hours in seconds.

However, Oracle will only keep 6 hours of old data if the UNDO log files are big enough, which depends on the size of the rollback segments and the amount of updates executed on the database.

So in addition to changing the undo retention time, you should also make sure that few concurrent updates are executed while your job is running. In particular, updates of the data your job is reading should be minimized.

If everything fails, increase the UNDO logs.

Related questions

Show column name and data type in Teradata?

Is there a way to get column name and data type in a Teradata SQL Assistant 15.0view? In Oracle 11g you can do something like desc tablename and get a script result set of the following : Name Null Type test_…

Read More
Teradata equivalent for lead and lag function of oracle

I have been working ot see the equivalent function for Oracle lead and lag function. The oracle lead would look like LEAD(col1.date,1,ADD_MONTHS(col1.DATE,12)) OVER(Partition By tab.a,tab.b,tab.c Order By tab.…

Read More
Move data from oracle to HDFS, process and move to Teradata from HDFS

My requirement is to Move data from Oracle to HDFS Process the data on HDFS Move processed data to Teradata. It is also required to do this entire processing every 15 minutes. The volume of source data may be close to 50 …

Read More