To Check Invalid Objects
====================
a)
set linesize 1000;
set pagesize 1000;
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;
exit;
b)
select count(*) from dba_objects where STATUS='INVALID';
c)
select owner,object_type,COUNT(*)
from dba_objects
where status='INVALID'
group by owner, object_type;
d) To check details
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;
e)Select * from DBA_OBJECTS where STATUS='INVALID' AND OWNER='APPS' and object_name like '%ITEM%' ORDER BY OBJECT_NAME ASC; (edit it as per your requirement)
To compile Invalid Objects
====================
@?/rdbms/admin/utlrp.sql;
In Windows:
SQL>@%ORACLE_HOME%/rdbms/admin/utlrp.sql
In Unix:
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
Or
To compile invalid objects using alter command.
==============================================
select OWNER||' '||OBJECT_NAME||' '||OBJECT_TYPE from dba_objects where OBJECT_name='DS_QUEUE_UTILITY_PRC';
O/p:
OWNER||''|| OBJECT_NAME ||''|| OBJECT_TYPE
------------------------------------------------------------------------------------
SCOTT DS_QUEUE_UTILITY_PRC PROCEDURE
ALTER PACKAGE SCOTT.ds_dlv_allocation_pkg COMPILE BODY; (To compile a package)
ALTER Procedure SCOTT.ds_queue_utility_prc COMPILE; (To compile a Procedure)
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;
To compile apps user invalid objects.
=============================
Login as application tier user
run adadmin and select option 3
Compile/Reload Applications Database Entities menu
option 1 “Compile Apps Schema''
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
NOTE : The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schemas. In case of an ORA-1555 error while running adcompsc.pls, restart the script.
The script can be run as follows :
cd $AD_TOP/sql
sqlplus @adcompsc.pls <SCHEMA_NAME> <SCHEMA_PASSWORD> %
Example :
sqlplus @adcompsc.pls apps <Password> %
Creating your own compilation script
===========================
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
Refrences
========
Troubleshooting Guide - invalid objects in the E-Business Suite Environment 11i and 12 (Doc ID 1325394.1)
Script: To verify Stored Procedures (Doc ID 1019928.6)
Script: To Recompile Invalid Objects (Doc ID 73995.1)
====================
a)
set linesize 1000;
set pagesize 1000;
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;
exit;
b)
select count(*) from dba_objects where STATUS='INVALID';
c)
select owner,object_type,COUNT(*)
from dba_objects
where status='INVALID'
group by owner, object_type;
d) To check details
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;
e)Select * from DBA_OBJECTS where STATUS='INVALID' AND OWNER='APPS' and object_name like '%ITEM%' ORDER BY OBJECT_NAME ASC; (edit it as per your requirement)
To compile Invalid Objects
====================
@?/rdbms/admin/utlrp.sql;
In Windows:
SQL>@%ORACLE_HOME%/rdbms/admin/utlrp.sql
In Unix:
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
Or
exec sys.utl_recomp.recomp_parallel(16);
Once compilation is done check invalid count.To compile invalid objects using alter command.
==============================================
select OWNER||' '||OBJECT_NAME||' '||OBJECT_TYPE from dba_objects where OBJECT_name='DS_QUEUE_UTILITY_PRC';
O/p:
OWNER||''|| OBJECT_NAME ||''|| OBJECT_TYPE
------------------------------------------------------------------------------------
SCOTT DS_QUEUE_UTILITY_PRC PROCEDURE
ALTER PACKAGE SCOTT.ds_dlv_allocation_pkg COMPILE BODY; (To compile a package)
ALTER Procedure SCOTT.ds_queue_utility_prc COMPILE; (To compile a Procedure)
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;
To compile apps user invalid objects.
=============================
Login as application tier user
run adadmin and select option 3
Compile/Reload Applications Database Entities menu
option 1 “Compile Apps Schema''
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
NOTE : The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schemas. In case of an ORA-1555 error while running adcompsc.pls, restart the script.
The script can be run as follows :
cd $AD_TOP/sql
sqlplus @adcompsc.pls <SCHEMA_NAME> <SCHEMA_PASSWORD> %
Example :
sqlplus @adcompsc.pls apps <Password> %
Creating your own compilation script
===========================
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
Refrences
========
Troubleshooting Guide - invalid objects in the E-Business Suite Environment 11i and 12 (Doc ID 1325394.1)
Script: To verify Stored Procedures (Doc ID 1019928.6)
Script: To Recompile Invalid Objects (Doc ID 73995.1)
No comments :
Post a Comment