Solved

How can I delete locks in Oracle Database?

Posted on 2002-07-30
13
4,324 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
dbms_crypto.decrypt   errors out 6 57
Create file system directory from Oracle 10g 4 41
how to tune the query 17 79
Oracle SQL-Queries on a RAC Database 3 37
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

740 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