Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Location
    India
    Posts
    217

    Unanswered: Tab lock and sql plus

    I am updating a table from sql plus. After the update command i have closed the sqlplus window
    without giving a COMMIT.

    Now in a new sqlplus window i am executing the same sql command. But the command is not getting
    executed. I can see the lock in OEM.

    I have the following questions,

    1. When does the lock gets released? is there a time period or I need to manually kill the session?
    2. How to handle this situation in a production environment?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    My guess is that closing the window (in one of MS Windows operating systems?) did not end Oracle session - the process is still there and, as you didn't perform commit nor rollback, table remains locked.

    Another session (your second SQL*Plus window) tries to update the same table but can not. It won't be able to do it until the first session releases the table.

    What to do? Kill first session. In production (do you really use SQL*Plus in production? Operators type commands?) you could first try to "LOCK TABLE table_name IN EXCLUSIVE MODE NOWAIT;" If it fails, you'll know what is going on and will be able to deal with it.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    If you can see the lock in OEM, then you know which session is holding the lock -- double click on the lock and select "KILL SESSION" (or go to sessions screen, right click on the session and select "KILL IMMEDIATE".

    Good luck!

    PS: In some situations, the lock does not get released (called DEADLOCK) -- here you would need to manually kill the session. (Or set in your resource manager a timeout).

    Last edited by LKBrwn_DBA; 09-22-05 at 18:56.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    Quote Originally Posted by Littlefoot
    do you really use SQL*Plus in production? Operators type commands?
    My users don't use sql*plus on production. I just wanted to know, is there a situation where a table is locked indefinitely and other users are not able to access it. If this thing happens at night time it will be difficult for me.

    Or like LKBrwn_DBA suggested setting resource manager a timeout solve this problem?

    Thank you both for your suggestions.

Posting Permissions

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