How can I delete locks in Oracle Database?

Hello Friends,

I use TOAD program to see locks in database.

Sometimes, I'd like to kill all of them because of the database problems. However I can't do it. Some of them can't be killed. I get 'the session was marked with killed-Ora31.

How can I kill the locks that I couldn't kill?

Best regards,

Hüseyin.


 
hbiyikAsked:
Who is Participating?
 
YensidModCommented:
This question is PAQed and no points refunded (of 200)

YensidMod
Expert Exchange Moderator
0
 
hayrabedianCommented:
Hi,

First of all you have to identify the sessions which holds the blocking locks (if you don't have any appropriate tool for this I am sure the guys will help with the script). Then, close or kill these sessions.


0
 
hayrabedianCommented:
when the session is marked for kill, but it takes to long time to be realy killed, you can query the v$session view for the PID of the process which runs your Oracle session in the operating system. when you find this PID you can kill the process in the operating system.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
schwertnerCommented:
Determine sid and id of a user session:

SELECT sid, serial# FROM v$session WHERE
username=‘SCOTT’;


The ALTER SYSTEM KILL SESSION command causes the background process
PMON to perform the following steps upon execution:
• Roll back the user’s current transaction
• Release all currently held table or row locks
• Free all resources currently reserved by the user


ALTER SYSTEM KILL SESSION ‘7,15’; ALTER SYSTEM KILL SESSION ‘7,15’;
0
 
bozkirliCommented:
This means that PMON is working on it and trying to clean up after the process, i.e. trying to roll back the transaction.  However, if there isn't enough resources -buffer blocks-, then PMON will wait until there is enough resources for to clean up.  You can wait, which might take up to 24 hours, or shutdown abort

Iyi sanslar...

Sadik Bozkirli


0
 
Mark GeerlingsDatabase AdministratorCommented:
As others have indicated, there is no way in Oracle to delete locks directly - you can only kill the sessions holding the lock(s).

Some sessions holding locks cannot be killed immediately.  In my experience this has happened most frequently when the session was running a PL\SQL process that includes a loop (often a cursor loop) and there is a long elapsed time between commits.  For this reason, I write all potentially long-running PL\SQL procedures to include a periodic check of a status flag in a "process_tracking" table that I created for this purpose.  This allows me (from another SQL*Plus session) to simply update the flag for a particular long-running PL\SQL processes which will tell it to abort the next time it checks this flag's value.  Otherwise if I simply kill the session running the PL\SQL procedure, the wait can be minutes or hours (or days) before it actually gets killed.
0
 
hayrabedianCommented:
Hi,

I want to use the chance to ask you guys about the solution which I mentioned before. I have used it several times without any subsequent problem. So, when Oracle (PMON) can not kill the session for long time, I just look for the operating system process, which runs the session to be killed.  It can easily be found by quering the V$session view. After that I am killing this operating system process(thread) and this immediately restores all occupied resources.
What is your opinion about this practice?
Thanks in advance for your replies!

Best regards,
Eddie
0
 
Mark GeerlingsDatabase AdministratorCommented:
Killing the operating system process or thread may be common practise in Unix, but I am not very familiar with any varieties of Unix or with Oracle on Unix.  I am most familiar with Oracle on Windows.  I did find a utility, orakill.exe, that can do the same thing for Oracle on Windows.  I have tried it a couple times in our test system and it seemed to work fine.  I haven't ever needed it in production.
0
 
hayrabedianCommented:
markgeer, thanks for the results for Windows platform.
I have used HP-Unix v.11 for my tests. Oracle could manage with the external killing of the process very well.
0
 
schwertnerCommented:
Good discussion!

I read in "Performance and tuning 8i" that Oracle automatically detects deadlocks (very reasonable!). After that:
1. Oracle rollbacks the statement caused the deadlock
2. Oracle releases 00060 message to the application
3. The user has to be advised (and helped) by the DBA to rollback the other statements in the transaction caused the deadlock.

It seems this is valid only for 8i.
0
 
hagenjCommented:
In Toad go to the DBA menu and pick the kill/trace window, from there pick the blocking lock tab, the the lock causing the problems and note the SID ( also note the sql statement at the bottom of the tab, that is most likely the statment causing the problem), now go to the processes tab the kill the process with that SID, Oracle will rollback as soon as the process is Killed.
0
 
AmmarCommented:
Hi,
 Run the below SQL commands on ur DB which will display the lock session informations

SET LINESIZE 130
SET PAGESIZE 66

COLUMN Object   FORMAT a30     HEADING 'Object'
COLUMN Type     FORMAT a4      HEADING 'Type'
COLUMN UserID   FORMAT a20     HEADING 'OS/Oracle'
COLUMN Hold     FORMAT a10     HEADING 'Hold'
COLUMN Program  FORMAT a35     HEADING 'Program'
COLUMN usercode FORMAT a12     HEADING 'SID/Serial#'
COLUMN WaitMin  FORMAT 999,999 HEADING 'Wait Time (minutes)'

SELECT
    a.osuser || ':' || a.username   UserID
  , a.sid || '/' || a.serial#       usercode
  , b.lock_type Type, b.mode_held   Hold
  , c.owner || '.' || c.object_name Object
  , a.program                       Program
  , ROUND(d.seconds_in_wait/60,2)   WaitMin
FROM
    v$session       a
  , dba_locks   b
  , dba_objects c
  , v$session_wait  d
WHERE
      a.sid        =  b.session_id
  AND b.lock_type  IN ('DML','DDL')
  AND b.lock_id1   =  c.object_id
  AND b.session_id  =  d.sid
/

Then kill the session by running the below SQL command
ALTER SYSTEM KILL SESSION a.sid,a.serial;

Thanx,
Ammar

0
 
patelgokulCommented:
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in one week.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help/closing.jsp

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER

patelgokul
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.