Link to home
Start Free TrialLog in
Avatar of tiendh
tiendh

asked on

Lock record

How can I know who lock record by
select * from  ... where ...
for update.
And what rowid of record.
ASKER CERTIFIED SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gauravm
gauravm
The simplest query (without any tech details)

SELECT
SYS.V_$LOCK.SID,
SERIAL#,
MACHINE,
PROGRAM,
OBJECT_NAME,
OSUSER
FROM
SYS.V_$LOCK,
SYS.V_$SESSION,
ALL_OBJECTS
WHERE
SYS.V_$LOCK.SID = SYS.V_$SESSION.SID
AND SYS.V_$LOCK.ID1 = ALL_OBJECTS.OBJECT_ID
AND SYS.V_$LOCK.TYPE = 'TM'
ORDER BY
SYS.V_$LOCK.SID,
MACHINE,
PROGRAM

You can then kill any session that is causing problems using

alter system kill session '<SID>,<serial#>'
Avatar of gauravm
gauravm
You have to run the above query from SYSTEM!
Avatar of tiendh
tiendh

ASKER

I want to get the value of lock record , can i ?
Avatar of gauravm
gauravm
what do u mean by "value"  .. is it the rowid ?
Avatar of tiendh
tiendh

ASKER

yes, it mean i want to know which row was lock
Avatar of gauravm
gauravm
SELECT row_wait_row#, row_wait_obj# FROM v$session WHERE sid= <SID which you have got from my above query>

You will get rowid in row_wait_row# and object id in row_wait_obj#

If a file i/o locked the row_wait_file# will give the requried value.

Hope it helps.