Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I use a certain rollback segment?

Re: How do I use a certain rollback segment?

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Tue, 23 Apr 2002 16:17:41 +0400
Message-ID: <aa3jb1$6vg$1@babylon.agtel.net>


Try adding explicit COMMIT before setting rollback segment:

commit;
set transaction use rollback segment rbsbig; <your transaction>
commit;
-- another segment may be used past this point

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"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
>
Received on Tue Apr 23 2002 - 07:17:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US