| ORA-01555 again - how is it possible? [message #104792] |
Thu, 03 February 2005 11:55 |
DK
Messages: 11
Registered: July 2002
|
Junior Member |
|
|
I am consistently getting the ORA-01555:snapshot too old: rollback segment number X with name "RBX.., and there is absolutely no reason I can see why it is happening.
I am only loading about 1 million records into an empty table every day across a database link (the table gets truncated every day before the data is loaded). If I extract the data it is only about 60-70mb in size. I have got 2 rollback segments for this tablespace, each 2.5gb in size(yes gb - even though we only load 350-400mb into it each day), and I also have 4 other rollback segments for the database, also 2.5gb each. Nothing else is happening to this table at the same time that it is loading the data, and no queries are being run against it either. I have even tried to run it whilst I am the only one in the database to ensure this. It happens to the same table every day, even thought there are about 50 other tables (smaller than this) that we copy across the link (not in parallel).
The load procedure is being generated by a ddl statement, so an implicit COMMIT is issued, we do not have a loop....commit routine.
I really do not see how the error can happen, as firstly, none of the messages in previous posts make sense in regards to loading such a small table with such large rollback segments, and I cannot see how it can be looping round and trying to overwrite an open transaction when it is such a small table with massive rollback segments (in comparison) that it can use. So how can the rollback information be overwritten before the end of the load? I have tried "bouncing" the db, and setting the table and tablespace to NOLOGGING, to no avail.
From the solutions given in one of the main ORA-01555 posts:
1) Increase size of rollback segment - done this to a large degree.
2) Reduce the number of commits - not issuing commits during the load.
3) Run the processing against a range of data rather than the whole table - not valid, loading data into an empty table.
4) Add additional rollback segments - done this, and extra large ones added.
5) Don't fetch across commits - not applicable, nothing else is touching the table at the same time.
6) Ensure the outer select does not revisit the same block at different times - no outer select or any queries running at the same time as the load.
Another thing that is confounding me, is the message "..rollback segment number 60 with name "RB5" "
I do not have a rollback segment named RB5!
Am I going mad? Am I missing something? Can anybody help with this?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
