Not able to drop the procedure

Question

I have created a function and procedure, the function is called in the procedure. According to the output of the function, in procedure I'm inserting data into some other table. Now that procedure is not executing nor getting dropped. If i try either of them in sql developer or cmd it is running and running with no response from db.

DROP PROCEDURE proc_insert_data;

Sql developer was just continuously running there for unlimited time. I checked in user_objects and all_objects: Object id is null and it is invalid.

And on your SQL Database, if you run an sp_who2, do you see any connections? You will need to kill 234 as an example. — Leptonator, Feb 26 '14 at 08:22

score 2 · Accepted Answer · answered Feb 26 '14 at 08:54

2

Perhaps your procedure is locked by some session which is not yet finished. In your case, I guess, the commit operation was not done in some another session.

You can check the blocking sessions by this:

SELECT
   s.blocking_session, 
   b.username blocking_username,
   b.osuser blocking_osuser,
   s.sid, 
   s.serial#, 
   s.seconds_in_wait,
   s.username
FROM
   v$session s,
   v$session b
WHERE
   s.blocking_session = b.sid
order by s.seconds_in_wait desc;

Then, after you recognize the session you can easily kill it or ask your administrator to do that.

answered Feb 26 '14 at 08:54

smnbbrv

23,502
9
78
109

thanks..it has helped me understand the real issue..but with that query i dint get any data..moreover i really dint get what that query fetches..can you please be little more elaborative – Naveen Feb 26 '14 at 09:19
so, in the view `v$session` there is a column `blocking_session` which says the session id which blocks your "session" which is dropping the procedure. If it returns nothing, maybe the way you should additionally try is to restart the database, of course if it is possible. – smnbbrv Feb 26 '14 at 09:26
restart db is not possible ...is there any other way?? – Naveen Feb 26 '14 at 09:37
try [these methods](http://www.oraclerecipes.com/monitoring/find-blocking-sessions/), maybe they'll help – smnbbrv Feb 26 '14 at 09:40
some of them are giving some ids which are not the object ids of my procedures..even if i get id, how to block that session?? – Naveen Feb 26 '14 at 10:00

score 2 · Answer 2 · answered Dec 26 '18 at 15:00

    Execute as Sys admin user, Below query lists out all the blocking sessions

  SELECT
       s.blocking_session, 
       b.username blocking_username,
       b.osuser blocking_osuser,
     s.sid, 
   s.serial#, 
   s.seconds_in_wait,
   s.username
FROM
   v$session s,
   v$session b
WHERE
   s.blocking_session = b.sid
order by s.seconds_in_wait desc;


    select sid,SERIAL#,status,username from v$session where sid=**[blocking_session]**;


   alter system disconnect session 'sid,SERIAL#' IMMEDIATE;

Not able to drop the procedure

2 Answers2