Re: How do I use a certain rollback segment?

"Guido Konsolke" <NoSpam_at_MyAccount.com> wrote in message news:<1019556177.416333_at_news.thyssen.com>...
> 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

Guido, the 'set transaction use rollback segment x' must be the first statement in a transaction. Since I have successfully used dbms_transaction.use_rollback_segment in a procedure I suspect that you need a 'rollback' or 'commit' immediately preceeding the call. Your ability to place these statements in your code depend on what you have done prior to calling the procedure, but I suspect that you can insert the necessary statement.

HTH -- Mark D Powell -- Received on Tue Apr 23 2002 - 08:42:13 CDT