The goal of the compile schema procedure is to attempt to make valid all INVALID procedures, packages, triggers, views, types, and so on in a schema. This procedure works (in 8.1.6 and before but not 8.1.7) by using the SYS.ORDER_OBJECT_BY_DEPENDENCY view which is supposed to return objects in the order they depend on eachother. If we compile the objects in the order this view returns the data ¡V at the end, all objects should be valid. This procedure runs the ALTER COMPILE command as the user who invoked the procedure (invokers rights).

It should be noted that COMPILE_SCHEMA demands you pass in a case sensitive username ¡V if you call:

scott@TKYTE816> exec dbms_utility.compile_schema( ¡¥scott¡¦ );

It is probable that nothing will happen (unless you have a lower case user named scott. You must pass in SCOTT.

There is however another issue with compile_schema in versions of the database between 8.1.5 to 8.1.6.2 (that is all 8.1.5 and 8.1.6.0 and 8.1.6.1 versions). If you have a Java enabled database, this will introduce some recursive dependencies into your system. This will cause COMPILE_SCHEMA to raise the error:

scott@TKYTE816> exec dbms_utility.compile_schema( user );
BEGIN dbms_utility.compile_schema( user ); END;

*
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data
ORA-06512: at "SYS.DBMS_UTILITY", line 195
ORA-06512: at line 1

If you encoutner this error, we can create our own COMPILE_SCHEMA procedure that behaves exactly as the real COMPILE_SCHEMA. We can do this by compiling the objects in any order we feel like it. It is a common misconception that we must compile objects in some specific order ¡V we can in fact do them in any old order and end up with the same outcome we would have had ordered by dependency. The logic is:

ć Pick any invalid object from a schema that we have not yet tried to compile
ć Compile it
ć Goto step one until there are no more invalid objects that we did not try to compile once

It is that simple ¡V we need no special ordering. That is because a side effect of compiling an invalid object is that all invalid objects it depends on will be compiled in order to validate this one. We just have to keep compiling objects until we have no more invalid ones (well, we might have invalid ones but that would be because they cannot be successfully compiled no matter what). What we might discover is that we need only to compile a SINGLE procedure to have 10 or 20 other objects get compiled. As long as we don¡¦t attempt to manually recompile those 10 or 20 other objects (that would invalidate the first object again) we are OK.

Since the implementation of this procedure is somewhat interesting, we¡¦ll demonstrate it here. We need to rely on an invokers rights routine to do the actual ¡§alter compile¡¨ command ¡V however we need access to the DBA_OBJECTS table to find the ¡§next¡¨ invalid object and report on the status of the just compiled object. We do not want the invoker of the routine necessarily to have to have access to DBA_OBJECTS. In order to achieve this, we will use a mixture of Invokers rights routines and Definers rights routines. We need to make sure that the top level routine, the one called by the end user, is the invokers rights routine however to ensure that roles are enabled and all.

Here is my implementation of a COMPILE_SCHEMA. The person who runs this script must have had SELECT granted to them on the SYS.DBA_OBJECTS view directly (refer to the Invokers Rights chapter for details on why this is). Since this is a SQLPlus script, with some SQLPlus directives in it ¡V I¡¦ll put the script in this time, not the results of actually running the script. I am using a SQLPlus macro variable to fill in the schema name as we compile objects. I am doing this because of the invokers rights routine (need to fully qualify objects if they should always access the SAME table regardless of who is running it) and the fact that I personally do not like to rely on public synonyms. The script will be given to you in pieces below with commentary in between:

column u new_val uname
select user u from dual;

drop table compile_schema_tmp
/

create global temporary table compile_schema_tmp
( object_name varchar2(30),
object_type varchar2(30),
constraint compile_schema_tmp_pk
primary key(object_name,object_type)
)
on commit preserve rows
/
grant all on compile_schema_tmp to public
/

We start the script by getting the currently logged in users username into a SQLPlus macro variable. We will use this later in our create or replace procedures below. The temporary table is used by our procedures to ¡§remember¡¨ what objects we have attempted to compile. We need to use ¡§on commit preserve rows¡¨ because of the fact that we are going to do DDL in our procedure (the alter compile command is DDL) and DDL commits. Next, we can start in on the procedures we need:

create or replace
procedure get_next_object_to_compile( p_username in varchar2,
p_cmd out varchar2,
p_obj out varchar2,
p_typ out varchar2 )
as
begin
select 'alter ' || object_type || ' '
|| p_username || '.' || object_name ||
decode( object_type, 'PACKAGE BODY', ' compile body',
' compile' ), object_name, object_type
into p_cmd, p_obj, p_typ
from dba_objects a
where owner = upper(p_username)
and status = 'INVALID'
and object_type <> 'UNDEFINED'
and not exists ( select null
from compile_schema_tmp b
where a.object_name = b.object_name
and a.object_type = b.object_type
)
and rownum = 1;

insert into compile_schema_tmp
( object_name, object_type )
values
( p_obj, p_typ );
end;
/

This is a definers rights procedure that accesses the DBA_OBJECTS view for us. This will return ¡§some¡¨ invalid object to be compiled ¡V as long as we have not yet attempted to compile it. It just finds the first one. As we retrieve them, we ¡§remember¡¨ them in our temporary table. Next we have our invokers rights routine that will actually do the compilation. This also shows why we needed the ¡§column u new_val uname¡¨ directive above ¡V we need to physically insert the OWNER of the temporary table in here to avoid having to use a synonym. Since we do this dynamically upon install ¡V it makes it better then a synonym:

create or replace procedure compile_schema( p_username in varchar2 )
authid current_user
as
l_cmd varchar2(512);
l_obj dba_objects.object_name%type;
l_typ dba_objects.object_type%type;
begin
delete from &uname..compile_schema_tmp;

loop
get_next_object_to_compile( p_username, l_cmd, l_obj, l_typ );

dbms_output.put_line( l_cmd );
begin
execute immediate l_cmd;
dbms_output.put_line( 'Successful' );
exception
when others then
dbms_output.put_line( sqlerrm );
end;
dbms_output.put_line( chr(9) );
end loop;

exception
when no_data_found then NULL;
end;
/
grant execute on compile_schema to public
/

And that is it ¡V now you can go into any schema that is able to compile some objects and execute:

scott@TKYTE816> exec tkyte.compile_schema('scott')
alter PROCEDURE scott.ANALYZE_MY_TABLES compile
Successful

alter PROCEDURE scott.CUST_LIST compile
ORA-24344: success with compilation error

alter TYPE scott.EMP_MASTER compile
ORA-24344: success with compilation error

alter PROCEDURE scott.FOO compile
Successful

alter PACKAGE scott.LOADLOBS compile
Successful

alter PROCEDURE scott.P compile
Successful

alter PROCEDURE scott.RUN_BY_JOBS compile
Successful

PL/SQL procedure successfully completed.

So, that shows me the objects it attempted to compile and the outcome. According to the above ¡V we compile 7 objects, 2 of which failed and 5 of which succeeded. We compiled them in any order, the order was simply not relevant. This procedure should work in all situations.