RE: Autonomous transaction not committed

> Yes. But he still needs to differentiate between different
> types of recursive transactions. Right? Some are autonomous,

I didn't see OP asking for that...

> some are code in PL/SQL block or procedure/trigger etc
> without using autonomous_transaction pragma, and some are

I don't really see a problem here (unless you have some transaction state object leak bug). Recursive/autonomous transactions don't just stay lingering on once the recursive call completes.

When you exit a block with active autonomous transaction, it will roll back with error below:

SQL> declare
  2 procedure p is pragma autonomous_transaction; begin insert into t values(9); end;
  3 begin
  4 p;
  5 end;
  6 /
declare
*
ERROR at line 1:

ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 2
ORA-06512: at line 4

> PDML. I wish v$transaction had a column or a bit in flag
> suggesting which type.

There's a flags column and also few additional columns decoded from flags in v$transaction, but not all types are covered... There's a "recursive" column there for example.

For better picture, a process state dump (search for "trans" state objects) may give more detail.

--
Tanel Poder
http://blog.tanelpoder.com


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