How do I use a certain rollback segment?

Hi there,

sorry for asking something as simple like this, but I'm sitting at a customer's client pc and have no books or internet available.

The environment: rdbms 8.1.7.0.0 on IBM AIX 4.3.

My problem is: I have to run a stored procedure in a given rollback segment named RBSBIG. So I tried using
"dbms_transaction.use_rollback_segment('RBSBIG');". The dba told me that
my transaction still used other rbs. So I tried
"set transaction use rollback segment rbsbig" ins SQL*PLUS and it responded ok.
Then I executed my procedure. The dba told me again that it was using a different segment.

Now I'm asking you gurus for a little help. I would like to determine the used rollback segment in my procedure and pass its name to sql*plus via dbms_output. Where can I find this info in the rdbms?

Alternatively please have a look at the piece of code that follows, maybe there's something wrong in my use of the dbms_transaction package.

• * * start of my procedure (snipped) create or replace procedure P_EH_konstest IS BEGIN -- START DER PROZEDUR dbms_transaction.use_rollback_segment('RBSBIG'); BEGIN insert into kons select * from t_eh_wache_umlauf_h_ind where rownum < 30001; commit; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR! ' || SUBSTR( SQLERRM, 1, 40 )); ROLLBACK; END; exception WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('FEHLER! ' || SUBSTR( SQLERRM, 1, 40 )); END P_EH_konstest;
• * * end of my procedure

Any thought or help would be appreciated.

tia,
Guido Received on Tue Apr 23 2002 - 05:04:41 CDT