Solved

How can I delete locks in Oracle Database?

Posted on 2002-07-30
13
4,093 Views
Last Modified: 2012-05-04
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.


 
0
Comment
Question by:hbiyik
  • 4
  • 2
  • 2
  • +5
13 Comments
 
LVL 3

Expert Comment

by:hayrabedian
Comment Utility
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
 
LVL 3

Expert Comment

by:hayrabedian
Comment Utility
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
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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
 

Expert Comment

by:bozkirli
Comment Utility
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
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
 
LVL 3

Expert Comment

by:hayrabedian
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
 
LVL 3

Expert Comment

by:hayrabedian
Comment Utility
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
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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
 
LVL 1

Expert Comment

by:hagenj
Comment Utility
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
 
LVL 1

Expert Comment

by:Ammar
Comment Utility
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
 
LVL 3

Expert Comment

by:patelgokul
Comment Utility
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
 

Accepted Solution

by:
YensidMod earned 0 total points
Comment Utility
This question is PAQed and no points refunded (of 200)

YensidMod
Expert Exchange Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now