Re: Thx so far, was: How do I use a certain rollback segment?

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:<aa5n6f$ijn$1_at_babylon.agtel.net>...
> Finding out currently used rollback segment will require digging through
> several V_$ views:
>
> select sid into mysid from sys.v_$mystat where rownum=1
> will give you your session id
> select saddr into mysaddr from sys.v_$session where sid = mysid
> will give you your session object address
> select xidusn into myxidusn from sys.v_$transaction where ses_addr = mysaddr
> will give you the USN of rollback segment used by current transaction
> in your session (please note that this value will only be available when
> rollback segment is actually used, that is, transaction modifies any data)
> and finally
> select name into myrbs from sys.v_$rollname where usn = myxidusn
> will give you the name of rollback segment used by current transaction in
> your session.
>
> You can combine these queries into single query, I gave PL/SQL notation
> for simplicity.
>
> If anyone knows shorter path to this info - please enlighten me either :)
>
> --
> 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:1019570972.731430_at_news.thyssen.com...
> > Hi Vladimir,Sybrand, Mark and all the others who might read this thread.
> >
> > First I wish to thank you for your guidance. I'm aware of the fact
> > that setting the rollback segment has to be the first statement in
> > the transaction. I even tried to set the rollback segment outside
> > of the procedure (in sql*plus) but the result was (according to my
> > dba) the same.
> >
> > But now (without changing anything), some hours later, everything works
> > fine. So I doubt if my dba wore his glasses in the morning ;-).
> >
> > Please give another glimpse of paradise by providing me with the solution
> > how to detect the used rollback segment (built-in package / system view).
> >
> > Thanks again to everyone,
> > Guido
> >

You can find a query at the cooperative FAQ http://www.jlcomp.demon.co.uk/faq/rolling_back.html Use the first query in the example above as is and remove the outer join from the second query to ensure correct results (but not distributed queries). I need to submit corrections/updates but have not had the time.

HTH -- Mark D Powell -- Received on Wed Apr 24 2002 - 08:36:25 CDT