RE: How to drop these procedures

Circular reference?????????????

Be carefull, just now we got rid of a serious problem because of circular reference.
We have been experiencing failure of export of one of our databases for the last
20 days.When we contacted Oracle support, they advised us to trace the objects
causing this problem and failed to explain what to do exactly they took the export of
sys.dependency$ and sys.obj$ objects in our troublesome database and finally

gave us the details of problematic objects and asked to DROP those objects and
recreate with proper coding(by avoiding circular reference).

And now we are able to export successfully.

We didn't face any problems in dropping them ofcourse.

Surendra
> -----Original Message-----
> From: Hannah.M.Doran_at_sb.com [SMTP:Hannah.M.Doran_at_sb.com]
> Sent: Friday, December 22, 2000 7:46 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: How to drop these procedures
>
>
>
> here's a long shot, but downlad Dbartisan evaluation copy. You can select
> multiple objects to delete. Hopefully it will do it simultaneouly and
> *trick*
> it out:>
>
>
>
>
>
> "Manivannan.M" <manivannan.m_at_tatainfotech.com> on 12/22/2000 07:00:37 AM
>
> Please respond to ORACLE-L_at_fatcity.com
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
>
>
>
>
> Hi Tim,
>
> It doesn't allow me to edit those procedures.
> Anyway I don't want them.
> When I try changing it and compile it gives me an
> Invalid Object Proc1(or proc2) error due its circular reference...:-)
>
>
>
>
> Manivannan Muthukrishnan.
>
> --------------------------------------------------------------------------
> ---
> ADDRESS: E-MAIL:
> 68/4, Site no:3, manivannan.m_at_tatainfotech.com
> 80 Feet road,
> (opp) Deccan Studio,
> Indiranagar, PHONE:
> Bangalore - 560038. 5284681 Ext: 464
> --------------------------------------------------------------------------
> ----
>
>
>
>
> On Fri, 22 Dec 2000, Tim Onions wrote:
>
> > How about recreating the procedures without the circluar references?
> >
> > eg
> > create or replace procedure proc1 as
> > begin
> > /* delete the reference to proc2 */
> > dbms_output.put_line('I am in Proc1');
> > end;
> > /
> > procedure proc2 as
> > begin
> > /* delete the reference to proc1_syn; */
> > dbms_output.put_line('I am in Proc1');
> > end;
> > /
> >
> > then drop procedure proc1/proc2?
> >
> > -----Original Message-----
> > Sent: 22 December 2000 10:00
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > Hi,
> >
> > One of my friend has this problem which I have mentioned below,
> >
> > There are two
> > procedures in the database and I'm giving here their contents.
> >
> > Procedure 1:
> >
> > create or replace procedure proc1 as
> > begin
> > proc2;
> > dbms_output.put_line('I am in Proc1');
> > end;
> >
> > Procedure 2:
> >
> > procedure proc2 as
> > begin
> > proc1_syn; ( proc1_syn is the synonym of procedure proc1).
> > dbms_output.put_line('I am in Proc1');
> > end;
> >
> > Both of them are invalid and rightly so. When you compile them you'll
> get
> > a deadlock error. But more importantly, I can't either delete these
> > procedures or edit and recompile them.
> > When try dropping the synonym of the procedure proc1 it
> > gave me the following error,
> >
> > ORA-00604: error occurred at recursive SQL level 1
> > ORA-01000: maximum open cursors exceeded.
> >
> > When I tried compiling I got this error.
> >
> > ORA-04045: errors during recompilation/revalidation of TBMS.PROC2
> > ORA-04020: deadlock detected while trying to lock object TBMS.PROC1
> >
> > When I tried dropping the procedures it gave me the following error,
> >
> > ORA-00600: internal error code, arguments: [4882], [9936752],
> [20285216],
> > [9775880], [], [], [], []
> >
> >
> > Thanks and Best Regards,
> > MDM
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Kamalakannan, D (CAP, GCF)
> > INET: D.Kamalakannan_at_gecapital.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Tim Onions
> > INET: tim.onions_at_speechmachines.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Manivannan.M
> INET: manivannan.m_at_tatainfotech.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Hannah.M.Doran_at_sb.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
Received on Fri Dec 22 2000 - 08:12:31 CST