Re: Script for dependencies.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/04/14
Message-ID: <33529098.18325911@newshost>#1/1

Have you looked at the dbms_utililty package that contains the compile_schema procedure?

procedure compile_schema(schema varchar2);
-- Compile all procedures, functions and packages in the specified

• schema. After calling this procedure you should select from view
• ALL_OBJECTS for items with status of 'INVALID' to
• see if all objects
• were successfully compiled. You may use the SQLDBA command "SHOW
  • ERRORS <type> <schema>.<name>" to see the errors assocated with
  • 'INVALID' objects.
  • Input arguments:
  • schema
  • Name of the schema.
  • Exceptions:
  • ORA-20000: Insufficient privileges for some object in this
  • schema.

This will get the procedures/packages/functions in a schema. If you run the following 2 scripts first, everything would get compiled that needs to (these gets the invalid views and triggers)

• comptrigg.sql ----------------------- set heading off set feedback off set linesize 80 spool xxtmpxx.sql select 'alter TRIGGER "' || object_name || '" compile;' from user_objects where object_type = 'TRIGGER' and status = 'INVALID' / spool off set heading on set feedback on @xxtmpxx.sql select 'show errors trigger ' || object_name from user_objects where object_type = 'TRIGGER' and status = 'INVALID' / ---------------- eof ------------------------------
• compview ------------------------------ set heading off set feedback off set linesize 80 spool xxtmpxx.sql select 'alter view "' || object_name || '" compile;' from user_objects where object_type = 'VIEW' and status = 'INVALID' / spool off set heading on set feedback on @xxtmpxx.sql select 'show errors view ' || object_name from user_objects where object_type = 'VIEW' and status = 'INVALID' / ----------------- eof ------------------------------

You should run compview and comptrigg first (and they may cause the automatic recompilation of some of the invalid procedures/pacakges).

On Mon, 14 Apr 1997 08:15:31 -0700, "Laurie D. Miller" <lmiller_at_lindy.stanford.edu> wrote:

>I was wondering if anyone has a script to identify all
>dependencies between objects, for a particular
>user schema?  I have looked at IDEPTREE, but it appears
>to identify dependencies for individual objects,
>not all objects owned by a user.  I would like
>to recompile packages, procedures, etc... for
>a user, but I do not know the correct order.
>
>Thank you,
>Laurie.
>-- 
>%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
>
>Laurie D. Miller           Stanford University
>Database Administrator     lmiller_at_lindy.stanford.edu

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Apr 14 1997 - 00:00:00 CDT