7 Replies Latest reply: Dec 15, 2010 10:21 AM by Chinar RSS

    compile all schemas

      Note that procedure "DBMS_UTILITY.COMPILE_SCHEMA" compiles only one given/input schema at a time, see description of it below.
      But is there possibility for lazy people to compile all schemas? I don't see such procedure. Do i really have to give all the 20 schema names myself, and compile them one-by-one?

      COMPILE_SCHEMA Procedure
      This procedure compiles all procedures, functions, packages, and triggers in the specified schema.
         schema          VARCHAR2,
         compile_all     BOOLEAN DEFAULT TRUE,
         reuse_settings  BOOLEAN DEFAULT FALSE);
      (Oracle 10g, Linux OS)

      So is this the only solution:
      select q'(dbms_utility.compile_schema(schema=>')' || username || q'(');)' from dba_users
        Dbms_Utility.Compile_Schema(SCHEMA => 'PUBLIC'); --copy paste others here
      Edited by: CharlesRoos on 15.12.2010 17:59
        • 1. Re: compile all schemas
          Maybe you can use UTL_RECOMP instead?

          If you omit the schema name then:
          "The schema in which to recompile invalid objects. If NULL, all invalid objects in the database are recompiled."

          Not sure if this is what you want...
          More information: http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php
          • 2. Re: compile all schemas
            Yes,you can write dynamic sql and can perform this for all schema s using dbms_utility.But also you can use utlrp.sql (located $ORACLE_HOME/rdbms/admin) file to compiling all database objects.
            • 3. Re: compile all schemas
              Hi, UTL_RECOMP seems to suit, it removed INVALID-flag from several schemas, thx.
              connect SYS/a@BOTCASINO
              • 4. Re: compile all schemas
                I also found file "utlrp.sql" but it seems totally empty, i don't see any sql there, why so?
                Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $ 
                Rem utlrp.sql
                Rem Copyright (c) 1998, 2003, Oracle Corporation.  All rights reserved.  
                Rem    NAME
                Rem      utlrp.sql - Recompile invalid objects
                Rem    DESCRIPTION
                Rem     This script recompiles invalid objects in the database.
                Rem     When run as one of the last steps during upgrade or downgrade,
                Rem     this script will validate all remaining invalid objects. It will
                Rem     also run a component validation procedure for each component in
                Rem     the database. See the README notes for your current release and
                Rem     the Oracle Database Upgrade book for more information about
                Rem     using utlrp.sql   
                Rem     Although invalid objects are automatically re-validated when used,
                Rem     it is useful to run this script after an upgrade or downgrade and
                Rem     after applying a patch. This minimizes latencies caused by
                Rem     on-demand recompilation. Oracle strongly recommends running this
                Rem     script after upgrades, downgrades and patches.
                Rem   NOTES
                Rem      * This script must be run using SQL*PLUS.
                Rem      * You must be connected AS SYSDBA to run this script.
                Rem      * There should be no other DDL on the database while running the
                Rem        script.  Not following this recommendation may lead to deadlocks.
                Rem   MODIFIED   (MM/DD/YY)
                Rem    gviswana    06/26/03 - Switch default to parallel if appropriate
                Rem    gviswana    06/12/03 - Switch default back to serial
                Rem    gviswana    05/20/03 - 2814808: Automatic parallelism tuning
                Rem    rburns      04/28/03 - timestamps and serveroutput for diagnostics
                Rem    gviswana    04/13/03 - utlrcmp.sql load -> catproc
                Rem    gviswana    06/25/02 - Add documentation
                Rem    gviswana    11/12/01 - Use utl_recomp.recomp_serial
                Rem    rdecker     11/09/01 - ADD ALTER library support FOR bug 1952368
                Rem    rburns      11/12/01 - validate all components after compiles
                Rem    rburns      11/06/01 - fix invalid CATPROC call
                Rem    rburns      09/29/01 - use 9.2.0
                Rem    rburns      09/20/01 - add check for CATPROC valid
                Rem    rburns      07/06/01 - get version from instance view
                Rem    rburns      05/09/01 - fix for use with 8.1.x
                Rem    arithikr    04/17/01 - 1703753: recompile object type# 29,32,33
                Rem    skabraha    09/25/00 - validate is now a keyword
                Rem    kosinski    06/14/00 - Persistent parameters
                Rem    skabraha    06/05/00 - validate tables also
                Rem    jdavison    04/11/00 - Modify usage notes for 8.2 changes.
                Rem    rshaikh     09/22/99 - quote name for recompile
                Rem    ncramesh    08/04/98 - change for sqlplus
                Rem    usundara    06/03/98 - merge from 8.0.5
                Rem    usundara    04/29/98 - creation (split from utlirp.sql).
                Rem                           Mark Ramacher (mramache) was the original
                Rem                           author of this script.
                Rem ===========================================================================
                Rem BEGIN utlrp.sql
                Rem ===========================================================================
                @@utlprp.sql 0
                Rem ===========================================================================
                Rem END utlrp.sql
                Rem ===========================================================================
                • 5. Re: compile all schemas
                  No this is not empty,see clearly in text of this sql you will see *@@utlprp.sql 0* it means this script actually call other. ;-)
                  • 6. Re: compile all schemas
                    Yes is see now.
                    Seems it does a little bit more than "utl_recomp.recomp_parallel(threads);", which seems useful too.
                    Thx again.
                    • 7. Re: compile all schemas
                      As result you will see that utlprp.sql script call below block
                         threads pls_integer := &&1;