Oracle - Handle Invalid Objects

Find Invalid Objects

 SELECT object_name, object_type  FROM dba_objects 
 WHERE  status = 'INVALID';
 FOO,VIEW
 BAR,VIEW

Compile Invalid Objects

 alter procedure pro_util compile ;

 alter function fun_util compile ;

 alter trigger tr_bch_trace compile ;

 alter view dab_view compile ;

 alter package dab_util compile ;

 alter package dab_util compile body;

 alter package dab_util compile package;

 alter view dab_view compile;

 alter public synonym ‘object_name' compile

Object Id is different so synonyms are invalid and need to be recreated

• Create table and synonyms

   create table TUX01
    (
      c1 number,
      c2 number
   );
   create or replace public synonym tux02 for tux01;
   create or replace synonym tux03 for tux01;

• Verify before status

   select * from dba_synonyms where synonym_name like 'TUX%';
   #	OWNER	SYNONYM_NAME	TABLE_OWNER	TABLE_NAME	DB_LINK
   1	PUBLIC	TUX02	        AHAADM	        TUX01
   2	AHAADM	TUX03	        AHAADM	        TUX01	

   select owner, object_name, object_type, status, object_id from dba_objects where object_name like 'TUX%';
   #	OWNER	OBJECT_NAME	OBJECT_TYPE	STATUS	OBJECT_ID
   1	PUBLIC	TUX02	        SYNONYM	        VALID	18137
   2	AHAADM	TUX01	        TABLE	        VALID	18139
   3	AHAADM	TUX03	        SYNONYM	        VALID	18138

• Drop the table

   select owner, object_name, object_type, status, object_id from dba_objects where object_name like 'TUX%';
   #	OWNER	OBJECT_NAME	OBJECT_TYPE	STATUS	OBJECT_ID
   1	PUBLIC	TUX02	        SYNONYM	        INVALID	18137
   2	AHAADM	TUX03	        SYNONYM	        INVALID	18138

• After recreate the table

   select owner, object_name, object_type, status, object_id from dba_objects where object_name like 'TUX%';
   #	OWNER	OBJECT_NAME	OBJECT_TYPE	STATUS	OBJECT_ID
   1	PUBLIC	TUX02	        SYNONYM	        INVALID	18137
   2	AHAADM	TUX01	        TABLE	        VALID	18140
   3	AHAADM	TUX03	        SYNONYM	        INVALID	18138