An error occurred while using sqoop when the OracleORA-01555 snapshot passed the old one

The ORA-01555 error was mentioned above, so now let's look at how ORA-01555 errors are produced. Since rollback segments are used cyclically, the transaction occupies

The ORA-01555 error was mentioned above, so now let's look at how ORA-01555 errors are produced. Since rollback segments are used cyclically, the transaction occupies

Error about Oracle ORA-01555 snapshot old

First understand under what circumstances Oracle will generate a ORA-01555 error:

Assume that there is a 60 million-Row Table testdb. It is estimated that it will take two hours to scan the entire table once. The reference process is as follows:

1. At one o'clock, user A sent A select * from testdb. No matter how testdb changes in the future, the correct result should be that user A will see the content at one o'clock.

2. At 01:30, user B executed the update command and updated the record of 4,100th million rows in the testdb table. At this time, user A's full table scan had not reached 4,100th million. There is no doubt that at this time, the record of 4,100th million rows is written into the rollback segment. Assume that the rollback segment is UNDOTS1. If user A's full table scan reaches 4,100th million rows, it should read the content at one o'clock from the rollback segment UNDOTS1 correctly.

3. At this time, user B submitted the operation he just performed, but at this time, the system can still provide user A with correct data, because the contents of the 4,100th million rows record are still in the UNDOTS1 segment, the system can find the correct data according to the SCN to the rollback segment, but note that, at this time, 4,100th million rows of records recorded in UNDOTS1 have undergone a major change: that is, the data of 4,100th million rows in the rollback segment UNDOTS1 may be overwritten at any time, because this record has been submitted!

4. As user A's query takes A long time and the business continues to run, the UNDOTS1 rollback segment is used by multiple different transactions, the extent in This rollback segment loops to the extent where 4,100th million rows of data are located. Because this record has been marked and submitted, this extent can be overwritten by other transactions!

5. At 01:45, user A's query finally reached 4,100th million rows. At this time, there were already 4th cases, and we had to go to the rollback segment UNDOTS1 to find the data, but has been overwritten, then there is a ORA-01555 error.

Cause analysis: the "Report" program has been executed for a long time. During the program query process, other users update the "Report" and the updated data is written into the rollback segment, when the program finds data in the rollback segment, it finds that the data has been overwritten, so there is a ORA-01555 error. In addition, "reports" program execution efficiency is not high will also cause ORA-01555 errors.