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
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 #425696 is a reply to message #425691] Sun, 11 October 2009 22:24 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
1) reboot OS
2) restart Oracle DB
3) ALTER SYSTEM KILL SESSION .....


Re: How to clear lock???? [message #425744 is a reply to message #425696] Mon, 12 October 2009 01:18 Go to previous messageGo to next message
im_vnew
Messages: 4
Registered: September 2009
Junior Member
Thanks for the response!!

Actually, this is our production database so bouncing wont be possible Sad

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
Re: How to clear lock???? [message #425756 is a reply to message #425744] Mon, 12 October 2009 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Apply point 3.

Regards
Michel
Re: How to clear lock???? [message #425774 is a reply to message #425691] Mon, 12 October 2009 03:03 Go to previous messageGo to next message
im_vnew
Messages: 4
Registered: September 2009
Junior Member
Thanks!!!
Got resolved.
Re: How to clear lock???? [message #425887 is a reply to message #425744] Mon, 12 October 2009 22:16 Go to previous messageGo to next message
trantuananh24hg
Messages: 664
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
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 Sad

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!
Re: How to clear lock???? [message #426033 is a reply to message #425887] Tue, 13 October 2009 21:25 Go to previous message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
trantuananh24hg Thanks Nice Reply

-Rahul
Previous Topic: Oracle Performance Manager
Next Topic: Analyze table hangs for hours
Goto Forum:
  


Current Time: Thu Sep 29 02:14:19 CDT 2016

Total time taken to generate the page: 0.18228 seconds