AW: V$Transaction entry

From: Petr Novak <Petr.Novak_at_trivadis.com>
Date: Thu, 17 Feb 2011 20:43:54 +0000
Message-ID: <09011014EB621E4CBC2536B62A1B64070244E0_at_SMXC001.trivadis.com>

Hallo Anjul,

there is no row in dba_2pc_pending, only in V$transaction

Mit freundlichen Grüssen
Petr Novak

---

Petr Novak
Trivadis GmbH
Lehrer-Wirth-Str. 4
D-81929 München

Phone     +49 89 99 27 59 30
Fax       +49 89 99 27 59 59
Mobile    +49 162 29 59 659

E-Mail petr.novak_at_trivadis.com
Internet http://www.trivadis.com

---

Trivadis GmbH, Geschäftssitz Stuttgart, Geschäftsführer: Urs Lehner, Urban Lankes, Thomas Riedel-Heine Amtsgericht Stuttgart, Handelsregister HRB 18089

---

Von: Anjul [anjulsahu_at_gmail.com]
Gesendet: Donnerstag, 17. Februar 2011 20:55 Bis: Petr Novak
Cc: oracle-l_at_freelists.org
Betreff: Re: V$Transaction entry

We had a similar issue in our production environment and we had to bounce the database to clear off those transactions. But recently i learned that we can force other sessions to rollback or commit. For doing that we force any transaction privilege.

ROLLBACK FORCE 'transaction_id' ;

transaction_id can be fetched from dba_2pc_pending view.

documented_at_/oracle_9i/doc/server.901/a90117/ds_txnma.htm

HTH
Anjul

On Fri, Feb 18, 2011 at 12:50 AM, Petr Novak <Petr.Novak_at_trivadis.com<mailto:Petr.Novak_at_trivadis.com>> wrote: Hi,

I have following problem:

update on table waits for lock (only one row tested) , there is no lock on the table in v$LOCK, analyze table validate structure gets ORA-00054, no rows in db_2pc_pending or pending_sessions , but there is one day old entry in V$TRANSACTION and V$LOCKED_OBJECT shows lock held by nonexistent session. Is there some possibility how to delete this entry from v$transaction without restarting the DB ? with dbms_transaction (I have no rights on it) ?

Best Regards,
Petr Novak

--

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

--

Anjul Sahu
Ph. +917869079958 | Gtalk: anjulsahu
Blog: http://anjulsahu.blogspot.com/
Facebook: http://www.facebook.com/anjuls

--

http://www.freelists.org/webpage/oracle-l Received on Thu Feb 17 2011 - 14:43:54 CST