Contents

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

NOTE: This is especially important when you implement the much more efficient CTAS (Create Table As Select) instead of updating millions of rows .



   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 table 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	        INVALID	18137
   2	AHAADM	TUX03	        SYNONYM	        INVALID	18138


   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

NOTE: The object-id is different so synonyms are invalid and need to be recreated.



See Also:
Oracle-Compile-Packages-Procedures-Functions
Oracle-System-Views

Status: Published Date: 2014-08-24 12:24:06 +0200 (So, 24 Aug 2014) Revision: 10

Copyright bei Andreas Haack (C) 2014. Diese Seite wird so wie sie ist zur Verfuegung gestellt, ohne irgenweche Garantien der Verwendbarkeit fuer bestimte Zwecke. Die auf dieser Seiten angebrachten Links liegen ausserhalb der redaktionellen Verantwortung von Andreas Haack und es wird keine Haftung oder Garantie uebernommen. Die Seiten sind Copyright (c) 2014 von Andreas Haack. Kein Teil darf ohne die schriftliche Einverstaendnis von Andreas Haack veroeffentlicht werden.
The page is provided 'as is' , without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fit- ness for a particular purpose and non-infringement. In no event shall Andreas Haack be liable for any claim, damages or other liability. This page is copyrighted property of Andreas Haack. Copyright by Andreas Haack (c) 2014 . No part of this page may be published without written permission for Andreas Haack. A hyper-link may created to this page but NOT to the embedded elements of this page. It may be freely downloaded for private purpose only as long as it is unaltered.