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

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

>