How to compile invalid objects

 
select  OWNER ,OBJECT_NAME ,CREATED ,OBJECT_TYPE from dba_objects where status<>'VALID';
 
OWNER       OBJECT_NAME     CREATED OBJECT_TYPE
------------------------------ ------------------------------ --------- -------------------
DBSNMP       MGMT_EMREP_OOB_MONITORING      27-MAY-15 SYNONYM
SQL> select * From dba_synonyms where owner='DBSNMP';
OWNER       SYNONYM_NAME                   TABLE_OWNER     TABLE_NAME                   DB_LINK
------------  ------------------                   -----------------     -------------                     ------------------------------ 
DBSNMP       MGMT_EMREP_OOB_MONITORING             SYSMAN    MGMT_EMREP_OOB_MONITORING
 SQL> SELECT *
FROM all_synonyms
WHERE owner = 'PUBLIC'
AND synonym_name = 'MGMT_EMREP_OOB_MONITORING'; 
no rows selected

SQL> drop    synonym MGMT_EMREP_OOB_MONITORING;
drop synonym MGMT_EMREP_OOB_MONITORING
                *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


SQL> drop    synonym MGMT_EMREP_OOB_MONITORING;
drop synonym MGMT_EMREP_OOB_MONITORING
                *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist

通过查看SYSMAN 用户是不存在的，当然 表也是不存在的MGMT_EMREP_OOB_MONITORING，但是DBSNMP又有sysman 表的私有同义词，怎么删除都删除不了，然后用下面的方式，删除了同义词

There are five ways to recompile invalid objects in schema.

1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL
5. Manually Recompile

DBMS_DDL.ALTER_COMPILE

Definition

This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]

Syntax

Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username

Name : Objects name

Example

SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');

PL/SQL procedure successfully completed.

DBMS_UTILITY.COMPILE_SCHEMA

Definition

This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

Syntax

Exec dbms_utility.compile_schema ( schema,compile all)

Schema : Database Username

Compile All : Object type ( procedure, function, packages,trigger)

Example

SQL> exec dbms_utility.compile_schema('SCOTT');

PL/SQL procedure successfully completed.

UTL_RECOMP

Definition

This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.

Syntax

Exec UTL_RECOMP.RECOMP_SERIAL ();

Example

SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();

PL/SQL procedure successfully completed.


Note: Required SYS user to run this package.

UTLRP.SQL scripts

Definition

Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.

Syntax
Located: $ORACLE_HOME/rdbms/admin

Example
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\UTLRP.SQL

TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2007-08-04 12:47:21


PL/SQL procedure successfully completed.


TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END 2007-08-04 12:47:26


PL/SQL procedure successfully completed.


Note: Required SYS user to run this script.
Recommended: After upgrade or migrate database.

Best Approach is manually recompiling all Invalid Objects

Spool recompile.sql

Select ‘alter ‘object_type’ ’object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And object_type IN (‘VIEW’,’SYNONYM’,
‘PROCEDURE’,’FUNCTION’,
‘PACKAGE’,’TRIGGER’);

Spool off
@recompile.sql


Note: VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER


Spool pkg_body.sql

Select ‘alter package ’object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type = ‘PACKAGE BODY’;

Spool off
@pkg_body.sql


Spool undefined.sql

select ‘alter materizlized view ’object_name’ compile;’
From user_objects
where status <> ‘VALID’
And object_type =‘UNDEFINED’;

Spool off
@undefined.sql


Spool javaclass.sql

Select ‘alter java class ’object_name’ resolve;’
from user_objects
where status <> ‘VALID’
And object_type =‘JAVA CLASS’;

Spool off
@javaclass.sql


Spool typebody.sql

Select ‘alter type ‘object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type =‘TYPE BODY’;

Spool off
@typebody.sql


Spool public_synonym.sql

Select ‘alter public synonym ‘object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

Spool off
@public_synonym.sql

Objects need to recompile are:

VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED (MATERIALIZED VIEW), JAVA CLASS, TYPE, TYPE BODY

http://dbataj.blogspot.jp/2007/08/how-to-compile-invalid-objects.html