ora-1555 during data purge

0 pts.
Tags:
Oracle 8i
We have a PL/SQL script that selects then purges data from a table row-by-row. We populated our Test environment with production data, ran it, and it worked fine. When we moved it to production and ran it against the same data, it failed with an ora-1555. We've set up a large rollback segment, and used the 'set transaction use rollback segment...' statement, to no avail. Has anyone else run into this? Any suggestions would be greatly appreciated. Thanks.
ASKED: July 20, 2004  10:51 AM
UPDATED: July 21, 2004  10:58 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Your large rollback segment still isn’t large enough. How often are you doing commits?

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JLeask
    Snapshot too old is probably being caused by the fact that you are processing a large cursor, performing deletes and performing commits. Unfortunately the commit now means that any rollback segment (RBS) blocks can be overwritten. Then your cursor requires a row which resides in a before image block on the RBS which has already been over written - ORA-1555. This can be aleviated by a large RBS but if you already have that then another solution is to re-open your cursor after every commit and commit only after a certain number of deletes.
    0 pointsBadges:
    report
  • Djp082
    If you are issuing commits and you want to continue to use the large rollback segment you will need to execute the set transaction after every commit otherwise Oracle will choose the next available RBS, which probably won't be the large one. If this database is running Oracle9 use automatic undo and set the undo_retention init parameter to the amount of time you expect the purge process to run.
    0 pointsBadges:
    report
  • 17051966
    Since you are using "set transaction use rollback segment...", you are using UNDO_MANAGEMENT = MANUAL. Then, UNDO_RETENTION parameter is irrelevant. I used to run a query to extract information about available free space for tablespaces. It ran fine under 8.1.7.4, but it always returns ORA-01555 after I upgraded to 9.2.0.4 and set UNDO_MANAGEMENT = AUTO with UNDO_RETENTION = 10800, i.e., 3 hours. The query used to take about one hour to run under 8.1.7.4. I opened a TAR with Oracle about six weeks ago. Oracle Tech Support has been unable to resolve the problem and opened a bug report for a future patch. Searching Oracle MetaLink I found that many DBAs have complained about this ORA-01555 error appearing under 9.2.0.4 with UNDO_MANAGEMENT = AUTO.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following