RE: Autonomous transaction not committed

I'm not sure whether you received my earlier reply to this thread, but I mentioned this about identifying owner sessions for transactions:

"Every recursive/autonomous transaction has its own transaction state object, so you can join v$transaction.ses_addr with v$session.saddr to find out which transactions belong to which session."

Yep, unless using XA then if a session is having multiple transactions, the newer ones are recursive or autonomous ones. Bit 0x20 in v$transaction.flag shows if a transaction is recursive transaction (recursive data dictionary update) but if this flag is not set, then that transaction should be an user-defined autonomous one.

I haven't checked whether audit transcations are considered as recursive or autonomous ones as far as the flag is considered.

Btw, Oracle uses recursive sessions which are created under your top-level session when recursive data dictionary updates are done, also I've seen recursive sessions created for plain autonomous transactions (maybe due a bug) in 10.2.0.1.

Tanel Poder
http://blog.tanelpoder.com

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yong Huang
> Sent: 30 January 2009 00:56
> To: oracle-l_at_freelists.org
> Cc: post.ethan_at_gmail.com
> Subject: Re: Autonomous transaction not committed
>
> Ethan,
>
> I think I find a way to identify a transaction to be
> autonomous, or identify the session that currently has an
> autonomous transaction. It's based on the fact that a session
> can only keep two transactions open at the same time, and the
> second one must be an autonomous transaction. (Does anybody
> object?) You can test this case like this:
>
> delete from t1 where rownum = 1;
> declare
> pragma autonomous_transaction;
> begin
> delete from t2 where rownum = 1;
> dbms_lock.sleep(10);
> end;
> /
>
> In another session, find the two transactions in
> v$transaction whose ses_addr is saddr of v$session for the
> above session. The one in v$transaction with a later
> start_time must be an autonomous transaction.
>
> If the second transaction started within the same second of
> the first one, you can't tell which one is autonomous. But
> you can still identify *the session* that has an autonomous
> transaction, i.e. the session having two transactions.
>
> My earlier assumption is wrong. I thought an autonomous
> transaction would open a new session (as in the case of
> parallel executions).
>
> Yong Huang
>
> --- On Wed, 1/21/09, Yong Huang <yong321_at_yahoo.com> wrote:
>
> > From: Yong Huang <yong321_at_yahoo.com>
> >
> > Sorry. What I proposed was not working. Bad QA.
> >
> > Yong
> >
> > --- On Wed, 1/21/09, Yong Huang <yong321_at_yahoo.com> wrote:
> >
> > > From: Yong Huang <yong321_at_yahoo.com>
> > >
> > > Interesting question. There's no statistic specifically for that.
> > > But you can infer from "implicit" session logons.
> > > Suppose your
> > > audit_trail is turned on. Audit session. Sample the value
> of 'logons
> > > cumulative' statistic, minus 'queries parallelized',
> minus number of
> > > times any job ran, minus the number of new audit trail
> entries for
> > > action_name 'LOGON'. Those "implicit"
> > > session logons should be,
> > > or at least include if I miss any possibility, autonomous
> > > transactions.
> > >
> > > Yong Huang
>
> > > > Is there a sesstat which I can look at to monitor the number of
> > > > active autonomous transactions in a session. I would like to
> > > > monitor this in trace or my debug package to resolve an issue.
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l