?
Solved

How can I delete locks in Oracle Database?

Posted on 2002-07-30
13
Medium Priority
?
4,514 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +5
13 Comments
 
LVL 3

Expert Comment

by:hayrabedian
ID: 7187848
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
ID: 7187854
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 48

Expert Comment

by:schwertner
ID: 7187895
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Expert Comment

by:bozkirli
ID: 7188072
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 35

Expert Comment

by:Mark Geerlings
ID: 7188438
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
ID: 7189955
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7190550
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
ID: 7190571
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 48

Expert Comment

by:schwertner
ID: 7192747
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
ID: 7194801
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
ID: 7226953
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
ID: 9426236
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
ID: 9489893
This question is PAQed and no points refunded (of 200)

YensidMod
Expert Exchange Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses

764 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