5 Replies Latest reply: Jan 10, 2013 2:32 AM by misterimran RSS

    Procedure hangs while create or replace!

    misterimran
      Dear All,

      My Database is Oracle 11g RAC.

      I did few changes in one of my procedure and wanted to re-create it using CREATE OR REPLACE procedure...

      The statement hangs for a long time and the concurrency in OEM raises up to a very high level. I tried to find the blocking sessions but for some time there was no blocking session.

      I also tried stopping the activities on the tables used in this procedure.

      Kindly suggest me what should I do in this situation. I have also tried running the query directly from the server.

      Regards, Imran
        • 1. Re: Procedure hangs while create or replace!
          moreajays
          Hi,

          check for any locks & kill it , execute on each instance or use gv$
          alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
          select /*+ CHOOSE */ a.sid, a.serial#,vp.spid,a.last_call_et,a.logon_time,a.status,sw.event, a.username,a.osuser, a.status, 
          a.program,a.MACHINE, a.type ptype,b.owner, b.object, b.type
          from v$session a, v$access b,v$session_wait sw, v$process vp
          where a.sid=b.sid and b.sid=sw.sid  and a.paddr=vp.addr
          and b.type<>'NON-EXISTENT'
          and (b.owner is not null) and (b.owner<>'SYSTEM')  and (b.owner<>'SYS')
          and upper(b.object) like '%&obj_name%'
          ORDER BY 3;
          Thanks,
          Ajay More
          http://moreajays.blogspot.com
          • 2. Re: Procedure hangs while create or replace!
            misterimran
            Thanks Ajay,

            Few comparison operators were missing in your query. I have highlighted them please see if they are correct:

            select /*+ CHOOSE */ a.sid, a.serial#,vp.spid,a.last_call_et,a.logon_time,a.status,sw.event,
            a.username,a.osuser, a.status,
            a.program,a.MACHINE, a.type ptype,b.owner, b.object, b.type
            from v$session a, v$access b,v$session_wait sw, v$process vp
            where a.sid=b.sid and b.sid=sw.sid and a.paddr=vp.addr
            and b.type *=* 'NON-EXISTENT'
            and (b.owner is not null) and (b.owner *=* 'SYSTEM') and (b.owner *=* 'SYS')
            and upper(b.object) like '%ManageAccountBillingPeriods%'
            ORDER BY 3;
            • 3. Re: Procedure hangs while create or replace!
              moreajays
              Ohh , Forum formatting has eat it , i copied correctly ;)

              Actually it is not equal to '< >' oprator & not equql to '=' , please correct & let me know if helpful

              Again its missing in below , will give you session killin commnd

              select /*+ CHOOSE */ 'alter system kill session '''||a.sid||','|| a.serial#||''';'
              from v$session a, v$access b
              where a.sid=b.sid
              and b.type<>'NON-EXISTENT'
              and (b.owner is not null) and (b.owner<>'SYSTEM') and (b.owner<>'SYS') and serial# <> 1
              and upper(b.object) like '%VSNL_CORP_TASK_READY_CMPL%';


              Thanks,
              Ajay More
              http://moreajays.blogspot.com
              • 4. Re: Procedure hangs while create or replace!
                Purvesh K
                Use != to represent Not Equals comparison instead of "Less than Greater Than" sign.
                • 5. Re: Procedure hangs while create or replace!
                  misterimran
                  I have found the blocking session with your query.

                  Thank you so much for your help.

                  Regards, Imran