Any good monitor script for LOCKED object V$LOCK

[d***@gmail.com]

Hi All,

Do anyone or any good site has:

I need to find a better lock monitoring
scripts to get segment info, etc.




Thanks,



DOM

[Mark D Powell]

Dom, and what does your current script look like? What version of
Oracle?

Try looking at V$LOCKED_OBJECT. You can directly join this to
v$session, v$process, v$transaction and from these to any of the v$sql
views to get all kinds of information.

But you were very unspecific in what kind of information you wanted to
see.

HTH -- Mark D Powell --

[d***@gmail.com]

Hi Mark,

I am on oracle 8.1.7.4.
Hopefully move to oracle 10g in Sept.

Basically, my work is very interested in finding out
the dba_segment, like which OBJECT (table) get LOCKED.

Yeah... I need to figure out what I exactly like to see.
Very true. I need to think about it more...
Agree with you from V$session, V$process, V$transaction and
then V$LOCKED_OBJECT could get some interesting result.



Dominica

[IANAL_VISTA]

Post by d***@gmail.com
Hi All,
I need to find a better lock monitoring
scripts to get segment info, etc.

Better than what?

Why are you fixated on lock monitoring?

[d***@gmail.com]

Hi,

Because in my work, there is a bunch of locked object going on.

That is why I would like to monitor the "locked object" and to find
out which segment.



Dominica

[IANAL_VISTA]

Post by d***@gmail.com
Hi,
Because in my work, there is a bunch of locked object going on.
That is why I would like to monitor the "locked object" and to find
out which segment.

Then query
desc v$locked_object
Name Null? Type
----------------------------------------- -------- -----------------------
XIDUSN NUMBER
XIDSLOT NUMBER
XIDSQN NUMBER
OBJECT_ID NUMBER
SESSION_ID NUMBER
ORACLE_USERNAME VARCHAR2(30)
OS_USER_NAME VARCHAR2(30)
PROCESS VARCHAR2(12)
LOCKED_MODE NUMBER

[DA Morgan]

Post by d***@gmail.com
Hi,
Because in my work, there is a bunch of locked object going on.
That is why I would like to monitor the "locked object" and to find
out which segment.
Dominica

Why are any objects being locked? This is Oracle not SQL Server. What
is the business case for performing any locking?

[Vincent Ventrone]

This query helps me:

column object format A25 heading 'DB Object'
column type format A10 heading 'Obj Type'
column term format A12 heading 'OS User'
column lcker format A12 heading 'Oracle ID'
column prog format A20 heading 'Client program'

set linesize 150
set pagesize 58
break on id1 skip 1 dup

SELECT
b.OWNER||'.'||b.OBJECT_NAME object,
b.OBJECT_TYPE type,
a.OS_USER_NAME term,
a.ORACLE_USERNAME lcker,
a.SESSION_ID "SID",
c.SERIAL# "Serial #",
c.PROGRAM prog,
NVL(c.lockwait,'HOLDING LOCK') "Lock Wait Addr.",
DECODE(a.LOCKED_MODE, 2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', 'UNKNOWN') "Lockmode"
FROM
V$LOCKED_OBJECT A,
ALL_OBJECTS B,
V$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID
AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
/

[d***@gmail.com]

Thank you Vincente,

I will try it out.

I am so busy lately, have not even get back to this topic yet.

Dominica

[Joel Garry]

Post by d***@gmail.com
Hi All,
I need to find a better lock monitoring
scripts to get segment info, etc.

The 9iR2 OEM has a pretty Lock Manager tool. Works fine with 8i, or
tells you if some feature is unavailable. Shows the rowid and what's
blocking who, also has a chart of blocking/locking.

jg
--
@home.com is bogus.
Glad that earthquake didn't bring down the computer while I was writing
this. http://earthquake.usgs.gov/recenteqsUS/Quakes/ci14155252.htm