1

How to drop the package when its got locked. I unable to find out the session to kill. so i want to re-create the package.

Error:

Error report: ORA-04021: timeout occurred while waiting to lock object temp.PRODUCT 04021. 00000 - "timeout occurred while waiting to lock object %s%s%s%s%s" *Cause: While waiting to lock a library object, a timeout is occurred. *Action: Retry the operation later.

spyga
  • 149
  • 1
  • 7
  • 22
  • What have you tried to find the session? You can't get anything useful out of `gv$access` and `gv$session`? Of course if something is actively using it then you might not really want to drop it right now. – Alex Poole Aug 19 '13 at 11:09
  • yes, i could see some sid is active.... – spyga Aug 19 '13 at 11:18
  • how can i kill this session id – spyga Aug 19 '13 at 11:18
  • 1
    See [this answer](http://stackoverflow.com/a/9546094/266304). This is not something you should do lightly though, make sure you know what you're doing and what the consequences are. Also if it's really busy then it might not die immediately, it could finish what it's doing and/or roll back any work it's done. – Alex Poole Aug 19 '13 at 11:24
  • @AlexPoole it has been running from morning ... so i just want to kill this session... but i unable to kill as i am getting below error. Error starting at line 7,819 in command: ALTER SYSTEM KILL SESSION '2148,26058' IMMEDIATE Error report: SQL Error: ORA-00030: User session ID does not exist. 00030. 00000 - "User session ID does not exist." *Cause: The user session ID no longer exists, probably because the session was logged out. *Action: Use a valid session ID. – spyga Aug 19 '13 at 12:18
  • @Saravanan If you're using RAC you may need to add the instance ID to the kill command. – Jon Heller Aug 19 '13 at 18:07

1 Answers1

0

There is a note on Metalink "Who holding my library cache pin" - or something like that. Also look at this article:

http://oracle-study-notes.blogspot.de/2009/05/find-session-holding-library-cache-lock.html

ibre5041
  • 4,903
  • 1
  • 20
  • 35