ora 1555 caused by two select statements?
hi guys,
I was running two select statements in different schemas, A and B
A and B both have the same tables created in their own schemas (but different data)
Schema C contains a table that is used by the query in A and B.
So A is running his select statement.
B is running the exact same select statement (in his own schema), but is using the output of his select to create a table i.e. CTAS
The only two things these two select statements share is C's table, which they both use to match a condition in their where clause
No other queries were running in the database.
Yet in the trace file we got:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small
This is totally beyond me. I just skimmed through an [article by Tom Kyte|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:44798632736844] where he says selects can generate undo, and that makes sense, but surely you can't get a ORA 1555 if nothing is updating or deleting any data!
thanks