Home » RDBMS Server » Security » How To Know Which Session Causing A TX Lock (Windows XP, Oracle 11g)
How To Know Which Session Causing A TX Lock [message #496481] Mon, 28 February 2011 09:32 Go to next message
oraQ
Messages: 57
Registered: January 2011
Member
I have to find out the cause of a lock where a particular session with some serial no is causing a TX level lock for certain duration. Can anyone guide me in that? Thanks in advance.
Re: How To Know Which Session Causing A TX Lock [message #496482 is a reply to message #496481] Mon, 28 February 2011 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

query V$LOCK
Re: How To Know Which Session Causing A TX Lock [message #496484 is a reply to message #496482] Mon, 28 February 2011 09:55 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Thanks BlackSwan. But, can you tell me hoe to find out which session is causing that TX level lock and how to kill that session?
Re: How To Know Which Session Causing A TX Lock [message #496485 is a reply to message #496484] Mon, 28 February 2011 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post SQL & results that show TX level lock is source of what ails you?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: How To Know Which Session Causing A TX Lock [message #496486 is a reply to message #496484] Mon, 28 February 2011 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does not "query V$LOCK" answer the question or are you unable to write a SELECT statement?

Why do you want to kill a session?
If you don't want anyone takes a lock then open your database in read-only mode.

Regards
Michel

[Updated on: Mon, 28 February 2011 09:59]

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496489 is a reply to message #496484] Mon, 28 February 2011 10:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
A useful supplied script that will list all sessions that are blocking other sessions is $ORACLE_HOME/rdbms/admin/utllockt.sql
Re: How To Know Which Session Causing A TX Lock [message #496490 is a reply to message #496489] Mon, 28 February 2011 10:36 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
I have some info like:
Quote:
Session xx with Serial# yyyy ,module=>DBMS_SCHEDULER and osuser=>oracle is blocking other sessions holding TX Lock for zz Minutes.

Though, I got the list of locked objects as suggested here, can the experts guide me what is that exactly? Thanks.
Re: How To Know Which Session Causing A TX Lock [message #496492 is a reply to message #496490] Mon, 28 February 2011 10:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
do as below so we can know complete Oracle version & OS name.

Post via COPY & PASTE complete results of
SELECT * from v$version;
Re: How To Know Which Session Causing A TX Lock [message #496493 is a reply to message #496490] Mon, 28 February 2011 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can the experts guide me what is that exactly?

Can you explain your question?
What don't you understand in the sentence?
Where did you get it from?

Regards
Michel
Re: How To Know Which Session Causing A TX Lock [message #496494 is a reply to message #496492] Mon, 28 February 2011 10:45 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Here is how I got as queried using SELECT * from v$version:

Quote:
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Re: How To Know Which Session Causing A TX Lock [message #496497 is a reply to message #496494] Mon, 28 February 2011 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How To Know Which Session Causing A TX Lock(Windows XP, Oracle 11g)

Title claims Windowx XP & V11, while DB reports differently

What is real & what is not in your posts?

You're On Your Own (YOYO)!
Re: How To Know Which Session Causing A TX Lock [message #496498 is a reply to message #496497] Mon, 28 February 2011 10:55 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
I mean to say client version is 10g and server is 11g.
Re: How To Know Which Session Causing A TX Lock [message #496502 is a reply to message #496498] Mon, 28 February 2011 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 28 February 2011 17:45
Quote:
can the experts guide me what is that exactly?

Can you explain your question?
What don't you understand in the sentence?
Where did you get it from?

Regards
Michel

Re: How To Know Which Session Causing A TX Lock [message #496503 is a reply to message #496498] Mon, 28 February 2011 11:13 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
According to your v$version the server is 10g - 10.2.0.4.0 to be exact.
Re: How To Know Which Session Causing A TX Lock [message #496505 is a reply to message #496503] Mon, 28 February 2011 11:20 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Michel, I want to know what does the mentioned description tell to me? Yeah I can query V$LOCK to find the related info, but that doesn't seem enough to explain the real cause what I said here. I got the following details from my superior.

Quote:
Session xx with Serial# yyyy ,module=>DBMS_SCHEDULER and osuser=>oracle is blocking other sessions holding TX Lock for zz Minutes.


Thanks.
Re: How To Know Which Session Causing A TX Lock [message #496508 is a reply to message #496505] Mon, 28 February 2011 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It says that session xx is holding a TX lock that is blocking other session which is an expected situation in a multisession system.

Regards
Michel
Re: How To Know Which Session Causing A TX Lock [message #496510 is a reply to message #496508] Mon, 28 February 2011 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>but that doesn't seem enough to explain the real cause

when all else fails, Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref761
Re: How To Know Which Session Causing A TX Lock [message #496619 is a reply to message #496510] Tue, 01 March 2011 07:53 Go to previous message
oraQ
Messages: 57
Registered: January 2011
Member
BlackSwan, the link you provided has very less info that can clear the thing I asked. I want to know how to find the conflicting sessions,locks of TX kind and what does the below statement say in practical.

Quote:
Session xx with Serial# yyyy ,module=>DBMS_SCHEDULER and osuser=>oracle is blocking other sessions holding TX Lock for zz Minutes.


Note: In reference to Michel's explanation:Quote:
It says that session xx is holding a TX lock that is blocking other session which is an expected situation in a multisession system.


Thanks.

Previous Topic: Query Regarding AUDIT_TRAIL
Next Topic: send oracle auditing to syslog
Goto Forum:
  


Current Time: Thu Mar 28 11:19:01 CDT 2024