Ora-01555

One of my query got failed with 01555. But I didn't see any reasons for this because ...

undo table space is 180G
undo retention is 2 hours

and the above said query which is running from ETL frame work has a target commit interval for every 100000 rows.

Thou source and target tables are more than 150M each and the whole query SLA is 19 hours still i didn't see any reason to tail with 01555 because the above undo & commit settings.

I have attached the query for your reference and the insert query is just a plain insert.

Please note that, this is the ONLY session running in the database during 01555.

Oracle 10.2.0.4
OS: HP
ETL: Informatica

Thanks,

You don't understand how the 1555 error occurs in the first place. When a commit takes place, the undo segment for that transaction becomes eligible for re-use by another transaction (or even yours). It gets overwritten, boom, you lose the snapshot of what data was in the past. Self-inflicted wound. What is the value of committing every 100000 records when all you need is one commit for the entire transaction (or whatever is going on at that point)?

Hi Stecal,

Thanks for the reply.

I totally agree with you in terms of how 01555 occurs.

Table A has 150 Mil rows.

In my scenario, one query with 10 connections (using informatica level partitioning using MOD and ROW_NUM) selecting the data from table A and inserting in table B and committing for 100000 records (again this is also informatica level configurable parameter).

I got 01555 on table A.

I am sure that no other process is doing any DML on table A when my job is running.

Could you please explain me what are the possible scenarios to get 01555 in this case?

Thanks,

You can set undo_retention to a higher value (and be prepared for the increase in size for the undo tablespace). Even with that step, if you commit, you just marked that segment as being eligible for use by something/someone else. You just reduce the chance of the error, not eliminate it.

Thanks Stecal.

Thanks,