Home » SQL & PL/SQL » SQL & PL/SQL » Unable to Recreate the Procedure (Oracle 10g)
Unable to Recreate the Procedure [message #656558] Tue, 11 October 2016 08:23 Go to next message
chat2raj.s
Messages: 112
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 #656559 is a reply to message #656558] Tue, 11 October 2016 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
chat2raj.s wrote on Tue, 11 October 2016 06:23
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.
Does procedure do any DML? If so does procedure issue COMMIT?

The reality is that when procedure does DML & does NOT issue COMMIT or ROLLBACK,
then this procedure can NOT be changed until after COMMIT or ROLLBACK is issued.
Re: Unable to Recreate the Procedure [message #656560 is a reply to message #656559] Tue, 11 October 2016 08:39 Go to previous messageGo to next message
chat2raj.s
Messages: 112
Registered: October 2010
Location: Chennai, India
Senior Member
Yes there are DML statements and finally a COMMIT also. Will i be able to identify the session which is currently using this objects, so that i can decide to wait or kill it.
Re: Unable to Recreate the Procedure [message #656562 is a reply to message #656560] Tue, 11 October 2016 08:47 Go to previous messageGo to next message
Bill B
Messages: 1802
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>%';
The package name but be in uppercase for the select to work!!
Re: Unable to Recreate the Procedure [message #656563 is a reply to message #656560] Tue, 11 October 2016 08:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
GV$ACCESS will tell you who is using procedure (or any other object).

SY.
Re: Unable to Recreate the Procedure [message #656564 is a reply to message #656563] Tue, 11 October 2016 08:51 Go to previous messageGo to next message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
Solomon Yakobson wrote on Tue, 11 October 2016 09:49
GV$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.
Re: Unable to Recreate the Procedure [message #656566 is a reply to message #656564] Tue, 11 October 2016 08:56 Go to previous messageGo to next message
chat2raj.s
Messages: 112
Registered: October 2010
Location: Chennai, India
Senior Member
Thanks Bill and Solomon. Sure, this helps me a lot.
Re: Unable to Recreate the Procedure [message #656567 is a reply to message #656566] Tue, 11 October 2016 08:57 Go to previous message
chat2raj.s
Messages: 112
Registered: October 2010
Location: Chennai, India
Senior Member
Thanks BlackSwan
Previous Topic: Easy Query
Next Topic: Create Table error
Goto Forum:
  


Current Time: Thu Oct 18 18:01:41 CDT 2018