-
Rollback segment error
Hello,
I am executing one sql file which populates our tables in database from other schema. My sql file contents are as follows.
Each procedure populates one table.
begin
set transaction use rollback segment BIG_R01;
-- EOD tables will be populated here
PKG_REP_EOD.prc_princ_co_share_hist;
PKG_REP_EOD.prc_unalloc_share_balances;
PKG_REP_EOD.prc_distribution_percentages;
PKG_REP_EOD.prc_company_trusts;
pkg_rep_eod.prc_historic_share_price;
end;
/
Evenif I am assigning big rollback segment why its giving following error.
ORA-01562: failed to extend rollback segment number 5
ORA-01628: max # extents (500) reached for rollback segment R04
Thanks
Paresh
-
isnt the error obvious, the your rollback segment has exceeded its maximum number of extents
-
But I am assigning Rollback Segment BIG_R01, then why it is using Rollback segment R04...Or do I have to specify Big_R01 for all Procedures. Because few procedures are getting executed properly...
I am getting error for last few procedures....
begin
set transaction use rollback segment BIG_R01;
PKG_REP_EOD.prc_princ_co_share_hist;
set transaction use rollback segment BIG_R01;
PKG_REP_EOD.prc_unalloc_share_balances;
set transaction use rollback segment BIG_R01;
PKG_REP_EOD.prc_distribution_percentages;
..............
end;
/
In every procedure I am deleting data in table and inserting data into table and then commiting it. So after commit, I think Oracle doesnt use BIG_R01 and its using any randowm Rollback segment and thats why i m getting error. Is my understanding is right???
Thanks
Paresh
-
why dont u take the other rollback segs offline and check out.
-
Originally posted by pareshg
So after commit, I think Oracle doesnt use BIG_R01 and its using any randowm Rollback segment and thats why i m getting error. Is my understanding is right???
Yes, that's exactly how it happens. SET TRANSACTION USE ROLLBACK SEGMENT is in effect only untill the current transaction finishes. So after every COMMIT or ROLLBACK you have to set transaction to a specific RB segment again if you wan't that new transaction to be tied with the specific RB.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|