Compile all invalid code with one command

Kenny Smith

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

    Requires Free Membership to View

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.

Related Topics: Oracle and SQL, VIEW ALL TOPICS

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: