This content has been marked as final. Show 13 replies
Hi,1 person found this helpful
if you are using 18.104.22.168
v$lock has this info
SQL> desc v$lock;
Name Null? Type
just create a query on this view v$lock and v$session.
you can try this one.
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
i hope it can help you.
select a.object_name ,c.USERNAME locking_session_name,
1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Shared Table',
5, 'Shared Row Exclusive',
6, 'Exclusive') locked_mode
from v$locked_object b,dba_objects a,v$session c
select * from dba_blockers
select * from dba_waiters
Thanks for advising but
I think V$lock will display information about all locks whose also which are not causing blocking other sessions
OEM >> INSTANCE >> LOCKS >> USER TYPE LOCKS
But i want lock causing blocking
OEM >> INSTANCE >> LOCKS >> BLOCKING/WAITING LOCKS
So what about qurying
What information do you believe that DBA_BLOCKERS and DBA_WAITERS are missing?
As i wrote i am using 9i
so may be these views are not in 9i available only in 10g .....?
so for 9i we need to query V$G...... view ...
Nope-- both of those views are available in 9i. [DBA_BLOCKERS|http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2209.htm#1308226] and [DBA_WAITERS|http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2414.htm#1315420] are both documented in the Oracle9i Database Reference.
Then how came i am not able to select this view ..
I am log in by system user.. in TOAD
.......it is saying OBJECT NOT FOUND
Can you post the four digit version number. try to execute the following script
$ORACLE_HOME\sysman\admin\smptsixx.sql has been run
- Pavan Kumar N
Edited by: Pavan Kumar on Feb 13, 2009 11:09 AM
Pavan what is this four digit version no.
where can find this ...?
11:47:01 zug7_real>select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod 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
- Pavan Kumar N
This i post already .see my first post .
this is 22.214.171.124.0
Have you tried to execute the script and tested ornot.
- PAvan Kumar N