DBA Hub

📋Steps in this guide1/6

Recompiling Invalid Schema Objects

This article presents several methods for recompiling invalid schema objects.

oracle miscconfigurationintermediate
by OracleDba
64 views
1

Identifying Invalid Objects

The view can be used to identify invalid objects using the following query. With this information you can decide which of the following recompilation methods is suitable for you.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;
2

The Manual Approach

For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types. Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command. An alternative approach is to use the package to perform the recompilations. This method is limited to PL/SQL objects, so it is not applicable for views.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
3

Custom Script

In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies. This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner || 
            '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner || 
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/
4

UTL_RECOMP

The package contains two procedures used to recompile invalid objects. As the names suggest, the procedure recompiles all the invalid objects one at a time, while the procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below. The usage notes for the parameters are listed below. - schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled. - threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value. - flags - Used for internal diagnostics and testing only. The following examples show how these procedures are used. There are a number of restrictions associated with the use of this package including: - Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete. - The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA. - The package expects the , , and to be present and valid. - Runnig DDL operations at the same time as this package may result in deadlocks.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
PROCEDURE RECOMP_SERIAL(
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
   threads  IN   PLS_INTEGER DEFAULT NULL,
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');
5

utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows. - 0 - The level of parallelism is derived based on the CPU_COUNT parameter. - 1 - The recompilation is run serially, one object at a time. - N - The recompilation is run in parallel with "N" number of threads. Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.
6

DBMS_UTILITY.compile_schema

The procedure in the package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus. For more information see: - UTL_RECOMP - DBMS_UTILITY.compile_schema Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT', compile_all => false);

Comments (0)

Please to add comments

No comments yet. Be the first to comment!