Re: dependecies between procedures, functions, views ...

From: Dmitry Sirotkin <sdmitry1_at_mail.ru>
Date: Wed, 9 Feb 2000 11:49:05 +0300
Message-ID: <87r9f0$g8g$1@news.rinet.ru>

Hi!

>Does anyone konw how I can find ... to
>recompile them in order (alter object_type object_name compile;) ? Is there
>a tool do it ?

Look at USER_DEPENDENCIES view and similar views (DBA_* and ALL_*).

The query can be written in the following way:

select uo.*
  from user_objects uo
 where uo.status <> 'VALID'
   and not exists (select 1 from user_dependencies ud, user_objects uo2

                    where ud.name = uo.object_name
                      and ud.type = uo.object_type
                      and ud.referenced_owner = USER
                      and ud.referenced_name = uo2.object_name
                      and ud.referenced_type = uo2.object_type
                      and uo2.status <> 'VALID'
                      )

 order by 3

It gives you information about all the invalid objects not depending of any other invalid objects in the same schema. But my not very wide experience tells me that such a query is going to work a lot of time especially if the schema containt a lot of objects. I think that invalid objects should be recompiled in the following order: PACKAGE HEADERS
VIEWS
FUNCTIONS
PROCEDURES
PACKAGE BODIES
TRIGGERS
what else I've forgotten? :)

For that task I've written a program with Delphi, which recompiles all the invalid objects in the specified schema in this order. It works thru BDE. I find it very useful and use it constantly. If you wish I could send it to you.

With best wishes,
Dmitry Sirotkin. Received on Wed Feb 09 2000 - 02:49:05 CST