How to release a locked object
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to release a locked object

Hybrid View

  1. #1
    Join Date
    Aug 2004
    Posts
    26

    Question How to release a locked object

    HI,

    I was doing an update and since it was taking a long time I stopped it but the session was not released, so I killed the session.
    I could see in v$locked_objects that the table is still locked.

    How do I release the lock.
    Any help is appreciated..

    - Patni

  2. #2
    Join Date
    Jan 2002
    Posts
    78
    Is this in production environment or in Devlopment.

    If its in Dev - rename the table with other name and again revert it to its original - Just check all the indexes and constraints before doing this

  3. #3
    Join Date
    Aug 2001
    Posts
    267
    Locks are Session based. If you kill that session you will be fine. It may take some while for Oracle to do rollback operation.
    Raghu

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    Are you killing the session from the os?
    If so check v$session to see if the session is still open.

    You can of course kill a session from oracle.
    When you kill the session from Oracle, Oracle still has to
    rollback any uncommitted changes from the session. It is
    probable that the lock will remain until the rollback is complete.

    SELECT SID,SERIAL#,STATUS,SERVER
    FROM V$SESSION
    WHERE USERNAME = 'JWARD';

    SID SERIAL# STATUS SERVER
    ----- -------- --------- ---------
    7 15 INACTIVE DEDICATED
    12 63 INACTIVE DEDICATED
    2 rows selected.

    ALTER SYSTEM KILL SESSION '7,15';
    Statement processed.

    SELECT SID, SERIAL#, STATUS, SERVER
    FROM V$SESSION
    WHERE USERNAME = 'JWARD';

    SID SERIAL# STATUS SERVER
    ----- -------- --------- ---------
    7 15 KILLED PSEUDO
    12 63 INACTIVE DEDICATED
    2 rows selected.

  5. #5
    Join Date
    Aug 2004
    Posts
    26
    Hi,

    I killed the session and the query to V$Session returned Status as KILLED, but still the table was locked.

    I had to just wait till ROLLBACK. I was just wondering if there is any way at user level to Force ROLLBACK or release the lock instantly.

    - Patni

    KILLED

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    I'm not aware of a way that you can immediately release the lock for a session that is disconnected and rolling back.

    Think of it like this, if you need a lock on a table for a transaction, is it fair to say that you need the lock until the transaction either is finished committing or finished rolling back?

    If you tune the transaction to make it run faster, then when it releases the lock isn't as critical.

    You should run the transaction with sql_trace and timed _statistics turned on and then run tkprof on the trace file to get an idea of what is happening in your transaction. You can also get good information from statspack.

  7. #7
    Join Date
    Aug 2004
    Posts
    26
    Thanks..I get your point..
    Will try tracing it next time.

    - Patni

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width