Rollback segment error

[pareshg]

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

[davey23uk]

isnt the error obvious, the your rollback segment has exceeded its maximum number of extents

[pareshg]

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

[niranjan]

why dont u take the other rollback segs offline and check out.

[jmodic]

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.

[pareshg]

Thanks guys...