RE: Autonomous transaction not committed

> Yes, I saw your earlier message. But I don't see how that
> solved the OP's question. We all know it's easy to associate
> a row (or two) in v$transaction with a row in v$session. But
> there's really no difference in all columns, including flag,
> of v$transaction (x$ktcxb.ktcxbflg which includes 'recursive'
> bit i.e. bit x20), between a regular transaction and an
> autonomous one. Let me know if I miss anything in your
> message. I only checked a few x$kt% tables, with no success.
> My test is on both 10.2.0.1 and 10.2.0.4. An autonomous
> transaction does not create its own session.

I ran my test case again on 10.2.0.1 on Windows and it looks I was wrong, the recursive session is created regardless autonomous transaction, when I execute a transaction from inside a package. It doesn't happen on 10.2.0.4 so I suspect its some bug which leaves this state object lingering around.

Note that recursive sessions can't be seen from v$session, you need to query x$ksuse for that with somethin like this:

select

    decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?')

   ,ksuudsna
   ,ksusepro
   ,ksspaown

    x$ksuse
where ksusepro=hextoraw('<process PADDR from v$process/v$session>')

The ksspaown field shows the owner of session state object. For recursive sessions, the owner is not the process state object (v$process.addr) but a call state object instead (which you'll see from a system/process state dump).

This helps to indicate whether a transaction was started from top-level call (an users SQL statement) or from a recursive call.

Note that the above explains recursive sessions and it doesn't' doesn't address OPs question though.

>
> Now here's something I have to do before I can sleep. I made
> a mistake again, in assuming OP's session always already has
> an active transaction before starting an autonomous one.
> That's a very wrong assumption! If a session starts a

Hehe, you're right, I assumed that as well, thanks for pointing this out.

> transaction as an autonomous one, there's no way we can tell
> it's autonomous or not. (If the sessions he's interested in
> are guaranteed to create a "dummy" transaction first e.g.
> insert into junk values (1), then what I proposed works, and
> it works only if the parent transaction is guaranteed not to
> commit or rollback, which sounds absurd.)

I looked into couple of system state dumps in hope to see the recursive transaction state objects belong under a call state object (not the session state object as normal top-level transcations), but it wasn't the case... So it looks like there's no easy way for figuring this out...

Tanel.

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