Home » RDBMS Server » Performance Tuning » How to clear lock???? (Oracle 10g)
How to clear lock???? [message #425691] |
Sun, 11 October 2009 21:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
im_vnew
Messages: 4 Registered: September 2009
|
Junior Member |
|
|
Hi All,
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?
Pleasseeeeeeeee
thnx!
|
|
|
|
|
|
|
Re: How to clear lock???? [message #425887 is a reply to message #425744] |
Mon, 12 October 2009 22:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
im_vnew wrote on Mon, 12 October 2009 13:18Thanks 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.
Thanks!
Regards,
im_vnew
"db file sequential read" is not a locking process, it's simply
Ask Tom
&
Oracle Document
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 (
pid,
sid,
ser#,
box,
username,
server,
inst_id,
os_user,
program )
AS
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,
b.server, b.inst_id,
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 (
sess,
id1,
id2,
lmode,
request,
type )
AS
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
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 (
username,
pid,
spid,
ora,
vlock,
type,
lmode,
wait )
AS
SELECT p.username, p.pid, p.spid, s.username ora,
DECODE (l2.TYPE,
'TX', 'TRANSACTION ROW-LEVEL',
'TS', 'TEMPORARY SEGMENT ',
'TD', 'TABLE LOCK',
'TM', 'ROW LOCK',
l2.TYPE
) vlock,
DECODE (l2.TYPE,
'TX', 'DML LOCK',
'TS', 'TEMPORARY SEGMENT',
'TD', DECODE (l2.lmode + l2.request,
4, 'PARSE ' || u.NAME || '.' || o.NAME,
6, 'DDL',
l2.lmode + l2.request
),
'TM', 'DML ' || u.NAME || '.' || o.NAME,
l2.TYPE
) TYPE,
DECODE (l2.lmode + l2.request,
2, 'RS',
3, 'RX',
4, 'S',
5, 'SRX',
6, 'X',
l2.lmode + l2.request
) lmode,
DECODE (l2.request, 0, NULL, 'WAIT') WAIT
FROM v$process p,
v$_lock l1,
v$lock l2,
v$resource r,
SYS.obj$ o,
SYS.user$ u,
v$session s
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!
|
|
|
|
Goto Forum:
Current Time: Mon Aug 26 13:56:06 CDT 2024
|