Home » RDBMS Server » Server Administration » Rollback Segments.
Rollback Segments. [message #370301] Wed, 18 August 1999 18:08 Go to next message
mvs
Messages: 1
Registered: August 1999
Junior Member
While running the application, if rollback segments get full, how to take care of the situation rhrough pl/sql? No new rollback segments are allowed to create.
Re: Rollback Segments. [message #370302 is a reply to message #370301] Thu, 19 August 1999 04:42 Go to previous message
Chris Hunt
Messages: 27
Registered: March 1999
Junior Member
As Prashanth says, if you have a rollback segment which is large enough to cope with your PL/SQL, use DBMS_TRANSACTION to pick it out.

If your rollback segments are all the same size (and none are big enough) you'll have to add some COMMIT statements to your PL/SQL procedure. If you're procedure currently looks like this:

...
FOR records IN my_cursor LOOP
   ...do some stuff...
END LOOP;
COMMIT;
...


You might think to change it to this

...
FOR records IN my_cursor LOOP
   ...do some stuff...
   COMMIT;
END LOOP;
...


Unfortunately, if you do this you're likely to end up with "Snapshot too old" errors as Oracle commits so many changes to the database that it's no longer sure that the version it's presenting in the cursor is a consistent one. What you need to do is commit a few records at a time:

...
commit_ct := 0;
FOR records IN my_cursor LOOP
   ...do some stuff...
   commit_ct := commit_ct + 1;
   IF commit_ct = 500 THEN
      COMMIT;
      commit_ct := 0;
   END IF
END LOOP;
...


This approach has the additional benefit of allowing you to monitor your procedure's progress. Of course you won't be able to roll back all the changes if things go wrong, so pick your commit point with care!

Previous Topic: Re: Select Row
Next Topic: Re: how to wrap packages?
Goto Forum:
  


Current Time: Thu Mar 28 18:23:58 CDT 2024