RE: Invalid objects

This is the cursor I find in the package for compile_schema :

cursor c1(schema varchar2) is

      select a.object_type, a.object_name, a.status
      from sys.order_object_by_dependency p, sys.dba_objects a
      where p.object_id = a.object_id
        and a.owner = c1.schema
      and (a.object_type = 'FUNCTION' or a.object_type = 'PROCEDURE' or
           a.object_type = 'PACKAGE' or a.object_type = 'PACKAGE BODY')
      order by dlevel desc;

So it looks like it's not done by alphabetical order, but in fact is does uses the order of dependency ....

> -----Original Message-----
> From: nlzanen1_at_ey.nl [SMTP:nlzanen1_at_ey.nl]
> Sent: Thursday, October 19, 2000 11:16 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Invalid objects
>
>
> Hi
>
>
> The Package dbms_utility compiles them in alphabetical order though and
> does therefore not validate every invalid object.
>
>
> Jack
>
>
>
>
> "Weerd Kirsten, E.C. de" <Kirsten.deWeerd_at_Oranjewoud.nl>@fatcity.com on
> 19-10-2000 11:40:52
>
> Please respond to ORACLE-L_at_fatcity.com
>
> Sent by: root_at_fatcity.com
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
>
> I have a script which uses the dependencies to determine the order of
> recompilation. Have often used it, and almost always recompiles every
> invalid the first time around. If anyone is interested ...
>
> I believe there is also a package :
>
> execute dbms_utility.compile_schema('SCHEMA')
>
> > -----Original Message-----
> > From: Diana Duncan [SMTP:Diana_at_fileFRENZY.com]
> > Sent: Thursday, October 19, 2000 12:35 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Invalid objects
> >
> > ...and, since there are often dependencies in objects that means you
> have
> > to
> > run the recompilation several times, I came up with the following little
> > database procedure, which also handles views, procedures and
> functions...
> >
> > create or replace procedure recomp is
> > cursor cnt_invalid_objects is
> > select count(*)
> > from user_objects
> > where status = 'INVALID';
> > cursor get_invalid_objects is
> > select object_name, object_type
> > from user_objects
> > where status = 'INVALID';
> > objRec get_invalid_objects%rowtype;
> > cnt number := 0;
> > iterations number := 0;
> > sqlStr varchar2(500) := null;
> > begin
> > open cnt_invalid_objects;
> > fetch cnt_invalid_objects into cnt;
> > close cnt_invalid_objects;
> > while (cnt > 0 and iterations < 5) loop
> > for objRec in get_invalid_objects loop
> > if (objRec.object_type = 'PACKAGE BODY')
> then
> > sqlStr := 'alter PACKAGE ' ||
> > objRec.object_name ||
> > ' compile body';
> > else
> > sqlStr := 'alter ' ||
> objRec.object_type ||
> > ' ' ||
> > objRec.object_name ||
> '
> compile';
> > end if;
> > execute immediate sqlStr;
> > end loop;
> > open cnt_invalid_objects;
> > fetch cnt_invalid_objects into cnt;
> > close cnt_invalid_objects;
> > iterations := iterations + 1;
> > end loop;
> > end recomp;
> > /
> >
> > Diana
> >
> > -----Original Message-----
> > Sent: Wednesday, October 18, 2000 3:06 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > Ruth,
> >
> > I would have to modified your script a little because the way to compile
> > the package and package body is different.
> >
> > it's
> >
> > select 'alter '||object_type||' '||owner||','||object_name||' compile;'
> > from dba_objects
> > where object_type not !='PACKAGE BODY' and status='INVALID'
> > union all
> > select 'alter package '||owner||'.'||object_name||' compile body;' from
> > dba_objects
> > where object_type='PACKAGE BODY' and status='INVALID';
> >
> > WInnie
> >
> >
> >
> >
> >
> > "Ruth Gramolini" <rgramolini_at_tax.state.vt.us>@fatcity.com on 10/18/2000
> > 11:50:24 AM
> >
> > Please respond to ORACLE-L_at_fatcity.com
> >
> > Sent by: root_at_fatcity.com
> >
> >
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > cc:
> >
> >
> >
> > select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
> > from
> > dba_objects where status ='INVALID';
> >
> > Spool the output of this query ( or cut and paste) and run it. It will
> > compile all invalid objects if there are no other problems.
> >
> > HTH,
> > Ruth
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, October 18, 2000 1:55 PM
> >
> >
> > >
> > > How can you fix invalid stored procedures?
> > >
> > > -----
> > > Sent using MailStart.com ( http://MailStart.Com/welcome.html )
> > > The FREE way to access your mailbox via any web browser, anywhere!
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author:
> > > INET: cemail_at_sprintmail.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: Ruth Gramolini
> > INET: rgramolini_at_tax.state.vt.us
> >
> > 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: Winnie_Liu_at_infonet.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: Diana Duncan
> > INET: Diana_at_fileFRENZY.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: Weerd Kirsten, E.C. de
> INET: Kirsten.deWeerd_at_Oranjewoud.nl
>
> 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).
>
>
>
>
> ===================================================================
> De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor
> de geadresseerde. Gebruik van deze informatie door anderen dan de
> geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding
> en/of verstrekking van deze informatie aan derden is niet toegestaan.
> Ernst & Young staat niet in voor de juiste en volledige overbrenging van
> de
> inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan.
> ===================================================================
> The information contained in this communication is confidential and may be
> legally privileged. It is intended solely for the use of the individual or
> entity to whom it is addressed and others authorised to receive it. If you
> are not the intended recipient you are hereby notified that any
> disclosure,
> copying, distribution or taking any action in reliance on the contents of
> this information is strictly prohibited and may be unlawful. Ernst &
> Young is neither liable for the proper and complete transmission of the
> information contained in this communication nor for any delay in its
> receipt.
> ===================================================================
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: nlzanen1_at_ey.nl
>
> 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
Received on Thu Oct 19 2000 - 05:36:55 CDT