Re: V$Transaction entry
From: Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
Date: Fri, 18 Feb 2011 09:50:29 +0100
Message-ID: <4D5E32D5.1030508_at_nordea.com>
Petr Novak pisze:
> 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
>
Date: Fri, 18 Feb 2011 09:50:29 +0100
Message-ID: <4D5E32D5.1030508_at_nordea.com>
Petr Novak pisze:
> 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
>
Petr, I am almost certain that few weeks ago You sent me few links of which one was very valuable in clearing that thing.
In general this issue happens when timeout settings on transaction manager are longer than distributed_lock_timeout.
According to the paper the transaction id shows in dba_2pc_pending after it is recognized as transaction in-doubt, etc - just look at the paper
Regards
Remigiusz
-- Pole nakazi ---------------------------------------------------------------------- Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com> pos : DBA at DIiUSI addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland phone : +48 58 667 17 43 mobile: +48 602 42 42 77 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 18 2011 - 02:50:29 CST