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

Source: Internet
Author: User
Tags sqoop
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.