v$trasaction.status is ACTIVE while v$session.status is KILLED
455096Aug 15 2008 — edited Aug 19 2008Does anybody know how to interpret the below result? I have a killed session that still owns an active transaction. I would have expected PMON to roll back the transaction, but its not happening. Now other sessions are being blocked by this supposedly killed session. This has happened to me before and I've only been able to clear things up by restarting the database.
v10.2.0.3
SQL> r
1 select s.sid, s.program, t.status as transaction_status, s.status as session_status,
2 s.lockwait, s.pq_status, s.SQL_ID, s.PLSQL_ENTRY_OBJECT_ID, s.type,
3 t.used_ublk as undo_blocks_used,
4 decode(bitand(t.flag,128),0,'NO','YES') rolling_back
5 from v$session s, v$transaction t
6* where s.taddr=t.addr
SID PROGRAM TRANSACTION_STAT SESSION_ LOCKWAIT PQ_STATU SQL_ID PLSQL_ENTRY_OBJECT_ID TYPE UNDO_BLOCKS_USED ROL
---------- ------------------------------------------------ ---------------- -------- ---------------- -------- ------------- --------------------- ---------- ---------------- ---
91 JDBC Thin Client ACTIVE KILLED ENABLED9av3ghghmnjdt 168793 USER 4 NO
1 row selected.
SQL>