Recompile the invalid object in Oracle

(Environment: Oracle 9i. All statements are executed under sqlplus)

 

1. query invalid objects

Queries the total number of invalid objects

 Select count (*) from dba_objects where status = 'invalid ';
Number of invalid objects in a category Query

Select owner, object_type, count (*) from dba_objects where status = 'invalid' group by owner, object_type;
Query object details
Select owner, object_name, object_type from dba_objects where status = 'invalid ';

 

2. manually recompile
Format of the recompilation statement:

Alter procedure <owner>. <procedure_name> compile; <br/> alter function <owner>. <function_name> compile; <br/> alter view <owner>. <view_name> compile; <br/> alter package <owner>. <package_name> compile; <br/> alter package <owner>. <package_name> compile body; <br/> alter materialized view <owner>. <package_name> compile;

 

Compile with Script

Views, packages, and other main objects:

Spool recompile. SQL <br/> select 'alter '| object_type | ''| Owner | '. '| object_name | 'compile;' from dba_objects where status = 'invalid'; <br/> spool off <br/> @ recompile. SQL

 

Package body

Spool pkg_body. SQL <br/> select 'alter package' | object_type | ''| Owner | '. '| object_name | 'compile body;' from dba_objects where status <> 'valid' and object_type = 'package body'; <br/> spool off <br/> @ pkg_body. SQL

 

Undefined object (not tested)

Spool undefined. SQL <br/> select alter materizlized view '| Owner | '. '| object_name | 'compile;' from dba_objects where status <> 'valid' and object_type = 'undefined'; <br/> spool off <br/> @ undefined. SQL

 

Java class (not tested)

Spool javaclass. SQL <br/> select 'alter Java class' | Owner | '. '| object_name | 'resolve;' from dba_objects where status <> 'valid' and object_type = 'java class'; <br/> spool off <br/> @ javaclass. SQL

 

Typebody

Spool typebody. SQL <br/> select 'alter type' | Owner | '. '| object_name | 'compile body;' from dba_objects where status <> 'valid' and object_type = 'Type body '; <br/> spool off <br/> @ typebody. SQL

 

Public_synonym (not tested)

Spool public_synonym. SQL <br/> select 'alter public synonym' | Owner | '. '| object_name | 'compile;' from dba_objects where status <> 'valid' and owner = 'public' and object_type = 'synonym '; <br/> spool off <br/> @ public_synonym. SQL

 

3. If an error occurs during compilation
If a compilation error occurs during recompilation, use "Show ERROR" to view the error and process the error.

 

 

In SDE, you can also try to move the package first and then regenerate it: <br/> SQL> Drop package body SDE. sde_util; <br/> SQL> Drop package SDE. sde_util; <br/> SQL> commit; <br/> SQL> exit; <br/> navigate to the $ sdehome/lib directory, log into SQL * Plus as the SDE user then execute the following commands: <br/> SQL> Start sde_util.sps; <br/> SQL> Start sde_util.spb; <br/> SQL> commit;

 

4. Other solutions
Use dbms_dll.alter_compile
Exec dbms_ddl.alter_compile ('Procedure ', 'Scott', 'test ');

Use dbms_utility.compile_schema
Exec dbms_utility.compile_schema ('Scott ');

Use utlrp. SQL
@ Utlrp. SQL