Re: Autonomous transaction not committed

From: Yong Huang <>
Date: Thu, 29 Jan 2009 14:56:27 -0800 (PST)
Message-ID: <>


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;
 pragma autonomous_transaction;
 delete from t2 where rownum = 1;

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 <> wrote:

> From: Yong Huang <>
> Sorry. What I proposed was not working. Bad QA.
> Yong
> --- On Wed, 1/21/09, Yong Huang <> wrote:
> > From: Yong Huang <>
> >
> > 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.

Received on Thu Jan 29 2009 - 16:56:27 CST

Original text of this message