Check for uncompiled or wrongly compiled objects
Execute this SQL command:
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;
Manual compilation
Execute following SQL commands to manually compile:
# PACKAGE:
alter package <PACKAGE NAME> compile;# PACKAGE BODY:
alter package <PACKAGE NAME> compile body;# PROCEDURE:
alter procedure <PROCEDURE NAME> compile;# FUNCTION:
alter function <FUNCTION NAME> compile;# TRIGGER:
alter trigger <TRIGGER NAME> compile;#VIEW:
alter view <VIEW NAME> compile;
Resolve issues during manual compilation
Find the compilation script
Example of SQL command which displays compilation script for view U_27819:
set long 10000
set pages 0
select dbms_metadata.get_ddl('VIEW','U_27819','SAPR3E') from dual;
Check used objects
Check all objects in the compilation script for any defects or if they are present. You can use SQL DESCRIBE command: