OraFAQ Forum: Security » How To Know Which Session Causing A TX Lock

Home » RDBMS Server » Security » How To Know Which Session Causing A TX Lock (Windows XP, Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How To Know Which Session Causing A TX Lock [message #496481]

Mon, 28 February 2011 09:32

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.

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496482 is a reply to message #496481]

Mon, 28 February 2011 09:33

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

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496484 is a reply to message #496482]

Mon, 28 February 2011 09:55

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?

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496485 is a reply to message #496484]

Mon, 28 February 2011 09:56

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/

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496486 is a reply to message #496484]

Mon, 28 February 2011 09:58

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

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

John Watson
Messages: 8997
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

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496490 is a reply to message #496489]

Mon, 28 February 2011 10:36

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.

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496492 is a reply to message #496490]

Mon, 28 February 2011 10:38

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;

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496493 is a reply to message #496490]

Mon, 28 February 2011 10:45

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496494 is a reply to message #496492]

Mon, 28 February 2011 10:45

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

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496497 is a reply to message #496494]

Mon, 28 February 2011 10:51

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)!

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496498 is a reply to message #496497]

Mon, 28 February 2011 10:55

oraQ
Messages: 57
Registered: January 2011

Member

I mean to say client version is 10g and server is 11g.

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496502 is a reply to message #496498]

Mon, 28 February 2011 11:12

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496503 is a reply to message #496498]

Mon, 28 February 2011 11:13

cookiemonster
Messages: 13975
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.

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496505 is a reply to message #496503]

Mon, 28 February 2011 11:20

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.

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496508 is a reply to message #496505]

Mon, 28 February 2011 11:27

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496510 is a reply to message #496508]

Mon, 28 February 2011 11:31

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

Report message to a moderator

Re: How To Know Which Session Causing A TX Lock [message #496619 is a reply to message #496510]

Tue, 01 March 2011 07:53

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 16 15:32:09 CDT 2026