Compile PL/SQL objects * For all users @utlrcmp.sql exec utl_recomp.recomp_parallel(15); select 'alter package '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='PACKAGE'; select 'alter package '||owner||'.'||object_name||' compile body;' from dba_objects where status='INVALID' and object_type='PACKAGE BODY'; select 'alter view '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='VIEW'; select 'alter procedure '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='PROCEDURE'; select 'alter trigger '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='TRIGGER'; select 'alter function '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='FUNCTION'; select 'alter java class '||owner||'."'||object_name||'" compile;' from dba_objects where status='INVALID' and object_type='JAVA CLASS'; select 'alter type '||owner||'.'||object_name||' compile body;' from dba_objects where status='INVALID' and object_type='TYPE BODY'; select 'alter type '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='TYPE'; select 'alter materialized view '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='MATERIALIZED VIEW'; select object_name from dba_objects where owner='APPACCNT' and object_type='PACKAGE BODY' minus select object_name from dba_objects where owner='APPACCNT' and object_type='PACKAGE'; ---------------------------------------------------------------------------------------------------- For a particular user: --2nd argument, default true, would "compile everything within the schema regardless of whether it is VALID" exec dbms_utility.compile_schema('APPACCNT',false) select 'alter package '||object_name||' compile;' from user_objects where status='INVALID' and object_type='PACKAGE'; select 'alter package '||object_name||' compile body;' from user_objects where status='INVALID' and object_type='PACKAGE BODY'; select 'alter view '||object_name||' compile;' from user_objects where status='INVALID' and object_type='VIEW'; select 'alter procedure '||object_name||' compile;' from user_objects where status='INVALID' and object_type='PROCEDURE'; select 'alter trigger '||object_name||' compile;' from user_objects where status='INVALID' and object_type='TRIGGER'; select 'alter function '||object_name||' compile;' from user_objects where status='INVALID' and object_type='FUNCTION'; select 'alter java class "'||object_name||'" compile;' from user_objects where status='INVALID' and object_type='JAVA CLASS'; If you wish to login as that user and don't have his password, just use proxy to login: alter user scott grant connect through dbajoe; conn dbajoe[scott]/dbajoepassword ...[finish the work]... conn dbajoe alter user scott revoke connect through dbajoe; ---------------------------------------------------------------------------------------------------- Just listing: select owner||'.'||object_name from dba_objects where status='INVALID' and object_type='PACKAGE'; select owner||'.'||object_name from dba_objects where status='INVALID' and object_type='PACKAGE BODY'; select owner||'.'||object_name from dba_objects where status='INVALID' and object_type='VIEW'; select owner||'.'||object_name from dba_objects where status='INVALID' and object_type='PROCEDURE'; select owner||'.'||object_name from dba_objects where status='INVALID' and object_type='TRIGGER'; select owner||'.'||object_name from dba_objects where status='INVALID' and object_type='FUNCTION'; select owner||'.'||object_name from dba_objects where status='INVALID' and object_type='JAVA CLASS'; select owner||'.'||object_name from dba_objects where status='INVALID' and object_type='TYPE BODY'; select owner||'.'||object_name from dba_objects where status='INVALID' and object_type='TYPE'; ---------------------------------------------------------------------------------------------------- Demo of invalid PL/SQL object being auto compiled on exec SQL> create table t (x int); Table created. SQL> create procedure p as i int; begin select x into i from t where rownum=1; end; 2 / Procedure created. SQL> select status from user_objects where object_name='P'; STATUS ------- VALID SQL> alter table t modify x number; <-- for this demo, we must modify this column, not e.g. add a column, to invalidate p (see "fine-grained dependency" at ../computer/UncommonSenseAboutCommonObjects.html) Table altered. SQL> select status from user_objects where object_name='P'; STATUS ------- INVALID SQL> exec p BEGIN p; END; * ERROR at line 1: ORA-01403: no data found ORA-06512: at "YHUANG.P", line 1 ORA-06512: at line 1 SQL> select status from user_objects where object_name='P'; STATUS ------- VALID This auto compilation works only if the object *can* be compiled without an error. Doing this manually prevents the situation that you find out some code needs to be debugged at the last moment when users are waiting for a solution. Unlike invalid PL/SQL objects, other "invalid" things in Oracle need human intervention. For example, an unusable index must be manually rebuilt. Sometimes (but not always) an invalid trigger must be compiled to resolve a library cache deadlock. Everything disabled must be explicitly enabled.