Home » RDBMS Server » Server Administration » Lock held by a session not found in v$session
Lock held by a session not found in v$session [message #231700] Wed, 18 April 2007 03:40 Go to next message
chewcm
Messages: 3
Registered: April 2007
Junior Member
See below a query I ran on v$locked_object:
SQL> select xidusn, xidslot, xidsqn, object_id, session_id, process, locked_mode
  2  from v$locked_object where object_id = 27351;

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------ -----------
         0          0          0      27351         91 1234                   3
        32          4     174461      27351        131                        3
         0          0          0      27351         22 1234                   3

The session (sid 131) is locking the table (object id 27351) and blocking sessions 22 and 91. However, I cannot find sid 131 in v$session. I also get no result if I query for sid 131 in v$lock. There's a corresponding record in v$transaction with status='PREPARED'. How do I get rid of the lock or kill the session?
Re: Lock held by a session not found in v$session [message #231708 is a reply to message #231700] Wed, 18 April 2007 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a distributed transaction and maybe your db is not the coordinator.
You have to wait for the coordinator to end the distributed commit or use dbms_transaction to force the commit or the rollback.

Regards
Michel
Re: Lock held by a session not found in v$session [message #231709 is a reply to message #231700] Wed, 18 April 2007 04:06 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have a look at metalink docid : 473256.995
Explains it very well
Re: Lock held by a session not found in v$session [message #231736 is a reply to message #231708] Wed, 18 April 2007 05:08 Go to previous messageGo to next message
chewcm
Messages: 3
Registered: April 2007
Junior Member
Michel Cadot wrote on Wed, 18 April 2007 17:04
This is a distributed transaction and maybe your db is not the coordinator.
You have to wait for the coordinator to end the distributed commit or use dbms_transaction to force the commit or the rollback.


I wanted to force commit or rollback the transaction. But it is not found in DBA_2PC_PENDING. So I'm not able to use COMMIT FORCE or ROLLBACK FORCE. Any other suggestions?
Re: Lock held by a session not found in v$session [message #231737 is a reply to message #231709] Wed, 18 April 2007 05:09 Go to previous message
chewcm
Messages: 3
Registered: April 2007
Junior Member
tahpush wrote on Wed, 18 April 2007 17:06
Have a look at metalink docid : 473256.995
Explains it very well

I do not have access to metalink. Are you able to email the doc to me? Thanks!
Previous Topic: Oracle patch information
Next Topic: Latest Path Update
Goto Forum:
  


Current Time: Mon Dec 05 12:39:49 CST 2016

Total time taken to generate the page: 0.08909 seconds