| Unable to Recreate the Procedure [message #656558] |
Tue, 11 October 2016 08:23  |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Dear Sir,
I am trying to Re create a procedure and it goes for a hang mode for long time. Then i have to randomly kill multiple sessions, after which am able to recreate the procedure.
How to identify, which session is holding my object (Procedure/trigger/view) so that i can kill only that one session to recreate the objects.
Thanks.
|
|
|
|
|
|
|
|
| Re: Unable to Recreate the Procedure [message #656562 is a reply to message #656560] |
Tue, 11 October 2016 08:47  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
A replaced stored procedure has to wait until all usage of the current procedure ends before it can be installed. This is normal behavior. If you are on a test machine use the following query to find all current users so you can kill them. If you are on a production machine, run the create/replace script in sql*plus and walk away until it installs it self or wait until the procedure is not in use (using the query below)
select x.sid
from v$session x, v$sqltext y
where x.sql_address = y.address
and upper(y.sql_text) like '%<package name>%';
|
|
|
|
|
|
| Re: Unable to Recreate the Procedure [message #656564 is a reply to message #656563] |
Tue, 11 October 2016 08:51 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Solomon Yakobson wrote on Tue, 11 October 2016 09:49GV$ACCESS will tell you who is using procedure (or any other object).
SY.
You learn something new every day. Thanks Solomon, using GV$ACCESS is much easier.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
