Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    47

    Unanswered: How to release locked record ?

    I read in documentation that one record will be locked automatically if user execute commands such as: INSERT, UPDATE . . . and release record after commit or rollback. My question are:
    1. How to check if there is any record is lock ?
    2. If user lost connection before commit or rollback, how can release the locked record ?
    Thanks

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    One easy way is via OEM; you can see all locks, and also the 'owner'. I've never tried it via SQL, but as OEM is just a GUI version of SQL, there must be a view that will also provide this information.

    Anyone know which one/s?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86
    Rename this file (TFSLKILL.txt) to .sql and run it in SQLplus or svrmgrl. It'll give you info about locked resources, sessions that locked it, and also info to kill em.
    Regards,

    Manf
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Manf,

    Good script!
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    To your second question - a lost connection results in a rollback, so all transaction dml is lost and locks are released automatically.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Dec 2003
    Posts
    47
    Thank you very much,
    And please help me with more one question:
    When I issue shutdown command, the database takes too long time to stop. I I think there are some lost connections stay in the system. At that time, I want to check and kill dead processes but I get an error that Oracle is on shutdown. What can I do to release them? Or I must check and kill them before shutdown database ?

  7. #7
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    There are different ways to shutdown a Db:

    "shutdown normal" - Oracle waits for all users to exit their sessions before closing down.

    "shutdown transactional" - Oracle waits for users to finish their current transaction beforeclosing down.

    "Shutdown immediate" - Oracle terminates all current transactions & performs a rollback.

    "Shutdown abort" - Same as "Immediate", but without the rollback.

    Each one is, in turn, faster then the previous - Which one are you using, and can you safely use a faster one?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

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