if any depending objects get missed then we get invalids.we can manually compile these invalids
by running relevant alter command.
we can also perform bulk compilation by running utlrp.sql.
OR
you can use following command to compile invalid objects parallel.
This need to run with oracle user.
execute utl_recomp.recomp_parallel(4);
It is good practice to count the invalid objects before/after compling them.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
40
SQL> execute utl_recomp.recomp_parallel(4);
PL/SQL procedure successfully completed.
SQL> show errors
No errors.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL>
OR
****
SQL> @/u01/db/tech_st/11.1.0/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2013-04-30 18:02:09
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2013-04-30 18:02:10
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL>
1. How many invalid objects are in the Database ?
SQL> select owner,object_type,COUNT(*) from dba_objects where status='INVALID' group by owner, object_type;
SYS PACKAGE BODY 1
ODM TRIGGER 1
ORDSYS PACKAGE BODY 4
PUBLIC SYNONYM 3
SYS PROCEDURE 1
XDB TRIGGER 5
APPS MATERIALIZED VIE 2
W
ODM PACKAGE BODY 7
ORDSYS VIEW 16
9 rows selected.
SQL>
====================
2. Check for details about the invalid objects :
column owner format A9
column object_name format A31
column object_type format A15
column last_ddl_time format A10
spool invalids.lst
select OWNER, OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME from dba_objects where status='INVALID' order by owner;
APPS LNS_LOAN_DTLS_ALL_MV MATERIALIZED VI 30-MAR-09
EW
APPS OE_ITEMS_MV MATERIALIZED VI 30-MAR-09
EW
ODM ODM_NAIVE_BAYES_MODEL PACKAGE BODY 18-JAN-09
ODM ODM_SUPERVISED_MODEL PACKAGE BODY 30-NOV-08
ODM ODM_ABN_MODEL PACKAGE BODY 30-NOV-08
ODM ODM_KM_CLUSTERING_MODEL PACKAGE BODY 18-JAN-09
ODM ODM_NAIVE_BAYES_APPLY PACKAGE BODY 30-NOV-08
ODM OP_COMMON PACKAGE BODY 30-NOV-08
ODM ODM_MINING_MODEL_TRIGGER TRIGGER 26-OCT-04
ODM ODM_MODEL_UTIL PACKAGE BODY 30-NOV-08
ORDSYS ORD_DICOM_ADMIN_PRV PACKAGE BODY 18-JAN-09
ORDSYS ORDIMG_PKG PACKAGE BODY 25-MAR-09
ORDSYS ORDIMGEXTCODEC_PKG PACKAGE BODY 25-MAR-09
ORDSYS ORD_DICOM_CT PACKAGE BODY 18-JAN-09
ORDSYS ORDDCM_CONFORMANCE_VLD_MSGS VIEW 18-JAN-09
ORDSYS ORDDCM_CT_ACTION_USR VIEW 18-JAN-09
ORDSYS ORDDCM_CT_PRED_PAR_USR VIEW 18-JAN-09
ORDSYS ORDDCM_CT_PRED_SET_USR VIEW 18-JAN-09
ORDSYS ORDDCM_CT_PRED_USR VIEW 18-JAN-09
ORDSYS ORDDCM_UID_DEFS_USR VIEW 18-JAN-09
ORDSYS ORDDCM_RT_PREF_PARAMS_USR VIEW 18-JAN-09
ORDSYS ORDDCM_ANON_ATTRS_USR VIEW 18-JAN-09
ORDSYS ORDDCM_DOCUMENTS VIEW 18-JAN-09
ORDSYS ORDDCM_CONSTRAINT_NAMES VIEW 18-JAN-09
ORDSYS ORDDCM_DOCS_USR VIEW 18-JAN-09
ORDSYS ORDDCM_STD_ATTRS_USR VIEW 18-JAN-09
ORDSYS ORDDCM_PRV_ATTRS_USR VIEW 18-JAN-09
ORDSYS ORDDCM_MAPPING_DOCS_USR VIEW 18-JAN-09
ORDSYS ORDDCM_MAPPED_PATHS_USR VIEW 18-JAN-09
ORDSYS ORDDCM_ANON_RULES_USR VIEW 18-JAN-09
PUBLIC ORDDCM_CONSTRAINT_NAMES SYNONYM 09-NOV-08
PUBLIC ORDDCM_CONFORMANCE_VLD_MSGS SYNONYM 09-NOV-08
PUBLIC ORDDCM_DOCUMENTS SYNONYM 09-NOV-08
SYS VALIDATE_ORDIM PROCEDURE 18-JAN-09
SYS PRVT_WORKLOAD PACKAGE BODY 25-MAR-09
XDB SERVLET$xd TRIGGER 09-NOV-08
XDB include345_TAB$xd TRIGGER 09-NOV-08
XDB Folder23_TAB$xd TRIGGER 09-NOV-08
XDB fallback340_TAB$xd TRIGGER 09-NOV-08
XDB fallback341_TAB$xd TRIGGER 09-NOV-08
40 rows selected.
The shown select statements will show you any invalid object - including Triggers, Java-Classes in the DB, Views and PL/SQL Packages.
*****************************
If you have identified a maximum of 10 invalid objects, it will be the best to try to compile them manually.
Please use following commands for the manual compilation, where the command for a package, a package body,
a View and a Java Class is listed :
alter package <owner>.<name of the package> compile;
alter package <owner>.<name of the package> compile body;
alter view <owner>.<name of the view> compile;
alter alter java class "<owner>.<name of Java Class>" resolve;
alter procedure <owner>.<name of the procedure> compile;
alter function <owner>.<name of the function> compile;
alter materialized view <owner>.<name of the materialized view> compile;
2. Scripts to compile invalid objects :
2.1 The script 'adcompsc.pls' under the $AD_TOP/sql :
The script 'adcompsc.pls' is installed under the $AD_TOP/sql - this script is executed when you run the AD_Utility 'adadmin'
and select to compile the invalid objects.
The script can be executed manually with following options :
1 - Schema to run in
2 - Password for schema
3 - Check errors for objects starting with #3
The script can be run as follows :
cd $AD_TOP/sql
sqlplus @adcompsc.pls <SCHEMA_NAME> <SCHEMA_PASSWORD> %
Example :
sqlplus @adcompsc.pls apps <Password> %
2.3 Creating your own compilation script
With the following script, you will create a new sql-script to compile the invalid objects, which can be used to compile your individual invalid objects.
set pagesize 0 head off feedb off echo off
spool validate_all.sql
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects where object_type in ('PROCEDURE','FUNCTION','VIEW','TRIGGER','MATERIALIZED VIEW')
and status='INVALID' order by owner
/
select 'alter package '||owner||'.'||object_name||' compile package;'
from dba_objects where object_type in ('PACKAGE')
and status='INVALID' order by owner
/
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects where object_type in ('PACKAGE BODY')
and status='INVALID' order by owner
/
select 'ALTER JAVA SOURCE "' || object_name || '" COMPILE;'
from user_objects where object_type = 'JAVA SOURCE' and status = 'INVALID';
/
select 'ALTER JAVA CLASS "' || object_name || '" RESOLVE;'
from user_objects where object_type = 'JAVA CLASS' and status = 'INVALID';
/
spool off
exit
The script 'validate_all.sql' should have been created in the directory, where you executed the select. You need to add the execution right on the created script, to start the compilation script.
chmod +x validate_all.sql
Then execute the script 'validate_all.sql' within a sqlplus session :
sqlplus apps/<Password>
