Compile package/function/procedure with dependency

Compiling PL/SQL objects by commands like

alter package pkg1 compile;
alter package pkg1 compile body;

does not consider to compile all PL/SQL object in dependency but rather will invalidate them. For example:

package pkg3 —–> pkg2 ——> pkg1 (where –> means for calling)

when compiling package pkg1, pkg2 and pkg3 will invalidated immediately.

In order to prevent recompiling several times of the dependency (which is maintained by the view DEPENDENCY$ in the SYS schema), use either this script

set verify off

set head off

set feed off

set pages 999

 create table temp_depend (object_id,referenced_object_id) as
select d.d_obj#, d.p_obj# from dependency$ d
where d.d_obj# in (select object_id from dba_objects
      where owner = '&&uname'
  and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'VIEW'
, 'TRIGGER') and status='INVALID')
/

 create or replace view ord_obj_by_depend (dlevel, object_id) as
select max(level), object_id from temp_depend
connect by object_id = prior referenced_object_id
group by object_id
/

spool compile.sql

 select
decode(OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;')
from dba_objects a, ord_obj_by_depend b
where a.owner = upper('&&uname') and
status='INVALID'
and
A.OBJECT_ID = B.OBJECT_ID(+) and
OBJECT_TYPE in ('PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW')
order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME;

spool off

PROMPT

PROMPT COMPILING INVALID OBJECTS ….

PROMPT

@compile.sql

 drop view ord_obj_by_depend;

drop table temp_depend;

undef uname

set head on

set feed on

set pages 24

set verify on

or an easier method to compile all invalid objects within a schema using UTL_RECOMP.recomp_parallel or DBMS_UTILITY.compile_schema.

PROCEDURE RECOMP_PARALLEL
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
THREADS                        BINARY_INTEGER          IN     DEFAULT
SCHEMA                         VARCHAR2                IN     DEFAULT
FLAGS                          BINARY_INTEGER          IN     DEFAULT
PROCEDURE RECOMP_SERIAL
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA                         VARCHAR2                IN     DEFAULT
FLAGS                          BINARY_INTEGER          IN     DEFAULT

exec UTL_RECOMP.recomp_parallel(4, 'SCHEMA_NAME');
PROCEDURE COMPILE_SCHEMA
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA                         VARCHAR2                IN
 COMPILE_ALL                    BOOLEAN                 IN     DEFAULT
 REUSE_SETTINGS                 BOOLEAN                 IN     DEFAULT

exec dbms_utility.compile_schema('SCHEMA_NAME');

or run script utlrp.sql to compile all invalid objects within a whole database

SQL> @?/rdbms/admin/utlrp.sql
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: