Compile all invalid code with one command |
 |
By Kenny Smith
15 Dec 2003 | IOUG |
|
|
The following is application development tip #4 from "30 tips in 30 minutes," brought to you by the IOUG. Return to the main page for more tips on this topic.
When a schema change is made to a table in the database, it invalidates all code and views that depend on that view. The views and code are automatically compiled when that view or code is accessed. But that real-time compile will take some time and may not succeed if the schema change prevents the code or view to compile successfully. You'd be wise to compile any invalid code or views after the schema change.
You can compile an entire schema using an Oracle supplied package procedure or via an Oracle supplied script.
The Oracle supplied package named DBMS_UTILITY has a procedure named COMPILE_SCHEMA. This procedure will compile all stored code, views, etc., for the schema provided.
The best way to compile all database objects that are invalid is to use a script in the $ORACLE_HOME/rdbms/admin directory named utlrp.sql. This script finds all objects in the data dictionary that are invalid and compiles them. This script is typically mentioned in patch notes but you can use it any time a schema change occurs.
Get more tips in minutes! Return to the main page.
About the author: Kenny Smith has been working with Oracle technology on HP servers for over a decade. He specializes in Oracle database architecture, database administration and development. He has presented at numerous Oracle conferences on two continents. He has published many articles describing Oracle solutions and has co-authored "Oracle backup and recovery 101" from Oracle Press.
IOUG: Become a member of the IOUG to access the paper referenced here and a repository of technical content created for Oracle users by Oracle users.
|
