Home » RDBMS Server » Performance Tuning » How to clear lock???? (Oracle 10g)
|How to clear lock???? [message #425691]
||Sun, 11 October 2009 21:04
Registered: September 2009
Currently im facing an issue.
My alert for db locks shows a lock on the database.
I checked the related sesions and sqls it's executing.
Generally, from these inputs , I determine which sessions need to be killed.
But today the o/p is as follows -
SID action status sql_text process
439 ACTIVE BEGIN rolling_updates; END; 1234
539 ACTIVE BEGIN rolling_updates; END; 1234
can anyone suggest how to deal with this?
|Re: How to clear lock???? [message #425887 is a reply to message #425744]
||Mon, 12 October 2009 22:16
Registered: January 2007
Location: Ha Noi, Viet Nam
im_vnew wrote on Mon, 12 October 2009 13:18|
Thanks for the response!!
Actually, this is our production database so bouncing wont be possible
To add to my query I would like to tell the event these are engaged into which I got from v$session. Those are -
439- enq: TX - row lock contention
539- db file sequential read.
Is it ok to kill the "db file sequential read" process, as it's locking '439' lock contention and secondly is running since 1 n hald day.
"db file sequential read" is not a locking process, it's simply
Because you kill the real lock session, then, you could not query what's object lock, what user cause lock... but, you can read & find some thing in user_dump_dest (I hope that you did not delete all of user dump file).
You may need some views:
1. Create view spid_and_pid as following:
CREATE OR REPLACE VIEW spid_and_pid (
SELECT SUBSTR (a.spid, 1, 9) pid, SUBSTR (b.SID, 1, 5) SID,
SUBSTR (b.serial#, 1, 5) ser#, SUBSTR (b.machine, 1, 6) box,
SUBSTR (b.username, 1, 10) username,
SUBSTR (b.osuser, 1, 8) os_user,
SUBSTR (b.program, 1, 30) program
FROM gv$session b, gv$process a
WHERE b.paddr = a.addr AND TYPE = 'USER'
ORDER BY spid
2. Create view lock_holder as following:
CREATE OR REPLACE VIEW lock_holder (
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request
3. Create view lock_object as following:
CREATE OR REPLACE VIEW lock_object (
SELECT p.username, p.pid, p.spid, s.username ora,
'TX', 'TRANSACTION ROW-LEVEL',
'TS', 'TEMPORARY SEGMENT ',
'TD', 'TABLE LOCK',
'TM', 'ROW LOCK',
'TX', 'DML LOCK',
'TS', 'TEMPORARY SEGMENT',
'TD', DECODE (l2.lmode + l2.request,
4, 'PARSE ' || u.NAME || '.' || o.NAME,
l2.lmode + l2.request
'TM', 'DML ' || u.NAME || '.' || o.NAME,
DECODE (l2.lmode + l2.request,
l2.lmode + l2.request
DECODE (l2.request, 0, NULL, 'WAIT') WAIT
FROM v$process p,
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.TYPE <> 'MR'
AND l2.TYPE <> 'RT'
AND r.id1 = o.obj#(+)
AND o.owner# = u.user#(+)
AND p.username LIKE NVL ('&&us_', '%')
ORDER BY 1, 2, 3, 4, 5
- Find PID from view spid_and_pid and you can see it in udump (SID_ORA_PID.trc).
- Find the session cause lock from view lock_holder
- Find the object lock from view lock_object.
Hope to help you!
Current Time: Tue Jan 17 07:42:27 CST 2017
Total time taken to generate the page: 0.13458 seconds