DBMS_UTILITY.COMPILE_SCHEMA in procedure

dbms_utility.compile_schema example
how to compile invalid objects in oracle 12c
how to compile invalid objects in oracle 11g
utl_recomp
how to fix invalid objects in oracle
alter procedure compile
alter package body compile
how to run utlrp.sql in oracle 12c

I've a PL/SQL procedure that is simply:

create or replace PROCEDURE COMPILE_VIEWS AS 
BEGIN
  DBMS_UTILITY.compile_schema('schema_name');
END COMPILE_VIEWS;

When I call the procedure, it seems to loop without actually compile any object...

Try it manually- so you can debug it as well.

SELECT 'alter ' 
  || DECODE( object_type,'PACKAGE BODY','PACKAGE','TYPE BODY','TYPE', object_type) 
  || ' ' 
  || object_name 
  || ' compile ' 
  ||DECODE( object_type,'PACKAGE BODY','body','TYPE BODY','body') 
  ||';' 
FROM all_objects 
WHERE owner ='schema_name' 
AND status !='VALID' ;

then alter the invalids one.

DBMS_UTILITY, COMPILE_SCHEMA Procedure. This procedure compiles all procedures, functions, packages, views and triggers in the specified schema. Syntax DBMS_UTILITY. DBMS_UTILITY runs with the privileges of the calling user for the NAME_RESOLVE Procedure and the COMPILE_SCHEMA Procedure. This is necessary so that the SQL works correctly. The package does not run as SYS. The privileges are checked using DBMS_DDL. The DBMS_UTILITY package uses the constants shown in Table 141-1, "DBMS_UTILITY Constants".

If schema objects are used by someone, those objects can't be compiled until they are released. That looks like an "indefinite loop" - nothing happens.

Compile them when nobody's working, i.e. during the off hours (at night?).

DBMS_UTILITY.COMPILE_SCHEMA in procedure, This procedure compiles all procedures, functions, packages, views and triggers in the specified schema. Syntax DBMS_UTILITY.COMPILE_SCHEMA ( schema  wolφi: When I run directly EXEC DBMS_UTILITY.compile_schema('schema_name'); it's done in less than 2-3 minutes, when I run the procedure, it runs during 20 minutes. I've tested that multiple times. – Cromm Jun 8 '18 at 4:57

You can monitoring the recompile invalid objects by repeating the following SQL statement:

SELECT COUNT(*) "NumberOfInvalidObject"
FROM DBA_OBJECTS
WHERE OWNER='Your_desired_schema_name_here' AND STATUS='INVALID';

Put that code in SQL Developer and pressing F5. You will realize the number of invalid objects are decreasing ...

DBMS_UTILITY.GET_TIME, Try it manually- so you can debug it as well. SELECT 'alter ' || DECODE( object_type,'PACKAGE BODY','PACKAGE','TYPE BODY','TYPE',  COMPILE_SCHEMA procedure compiles all procedures, functions, packages, and triggers 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.

Well, I've found my answer somewhere else. As someone pointed out, only views need to be compile, so, here is the code (working), that is an alternative to DBMS_UTILITY.COMPILE_SCHEMA:

create or replace PROCEDURE COMPILE_VIEWS AS 

  cursor OBJECTS is
  select object_name, object_type, status
  from USER_OBJECTS
  where object_type in ('VIEW')
    and object_name != 'COMPILE_SCHEMA'
    and status = 'INVALID'
  order by object_name;
  --
  cmd            varchar2(300);
  msg            varchar2(2000);
  COMPILE_ERROR  exception;
  pragma EXCEPTION_INIT (COMPILE_ERROR, -24344);
begin
  dbms_output.enable(1000000);

  for o in objects loop
        cmd := 'alter '||o.object_type||' '||o.object_name||' compile';
      begin
        execute immediate cmd;
        msg := rpad('Compiled '||lower(o.object_type),25)||o.object_name;
      exception 
        when COMPILE_ERROR then
          msg := rpad('Compiled '||lower(o.object_type),25)||rpad(o.object_name,35)||' COMPILE ERROR';        
        when OTHERS then
          msg := rpad('Compiled '||lower(o.object_type),25)||rpad(o.object_name,35)||sqlerrm;
      end;
      dbms_output.put_line(rtrim(msg));
  end loop;

END COMPILE_VIEWS;

Then run it:

begin
  COMPILE_VIEWS();
end;

Works like a charm.

Script: Back Trace Exception to Line That Raised It, function, added in Oracle Database 10g Release 2, is a critical subprogram to call when logging exceptions. It returns a string that traces the error back to the line on which it was raised! It does; at least in 11.2.0.3, so I suppose there could have been a bug in the base release. if I create an invalid package, in this case because it refers to a table that doesn't exist:

ALTER PACKAGE Statement, , the database drops all persistent compiler switch settings, retrieves them again from the session, and stores them at the end of compilation. The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus. The example below shows how it is called from SQL*Plus.

Why does DBMS_UTILITY.COMPILE_SCHEMA break function , After mass structure updates, some views/procedures became invalid. So I just recompiled them with: exec dbms_utility.compile_schema( USER ). Welcome to the Duxbury Bay Maritime School! The Duxbury Bay Maritime School is a nonprofit organization that exists as a gateway for all to Duxbury Bay. We have educational and recreational programs for both kids and adults with the majority of our programming taking place during the summer season.

How to Compile all Invalid Objects in Oracle, Using the dbms_utility.compile_schema procedure and leveraging the force in the create view statements, most of my releases look like the 

Comments
  • How do you notice that it is looping, and how do you see that objects are not compiled?
  • I'm curious: your procedure name is COMPILE_VIEWS, but you're recompiling every object in the schema. Does this schema contain only views?
  • wolφi: When I run directly EXEC DBMS_UTILITY.compile_schema('schema_name'); it's done in less than 2-3 minutes, when I run the procedure, it runs during 20 minutes. I've tested that multiple times.
  • Bob Jarvis: Actually, no, but the views represent something like 30-40 % of the total objects (all included), and this is for compiling the views I run that.