Oracle7 Server Application Developer's Guide

Manually Recompiling

Oracle dynamically recompiles an invalid view or PL/SQL program unit the next time it is used. Alternatively, you can force the compilation of an invalid view or program unit using the appropriate SQL command with the COMPILE parameter.

Forced compilations are most often used to test for errors when it is known that a dependent view or program unit is invalid, but is not currently being used; therefore, automatic recompilation would not otherwise occur until the view or program unit is executed.

Invalid dependent objects can be identified by querying the data dictionary views USER_OBJECTS, ALL_OBJECTS, and DBA_OBJECTS; see "Listing Dependency Management Information" for examples.

Manually Recompiling Views

To recompile a view, use the ALTER VIEW command with the COMPILE parameter. The following statement recompiles the view EMP_DEPT contained in your schema:

ALTER VIEW emp_dept COMPILE;

Privileges Required to Recompile a View

To manually recompile a view, the view must be contained in your schema or you must have the ALTER ANY TABLE system privilege.

Manually Recompiling Procedures and Functions

To recompile a procedure or function (standalone), use the ALTER PROCEDURE or ALTER FUNCTION command with the COMPILE clause. For example, the following statement recompiles the stored procedure UPDATE_SALARY contained in your schema:

ALTER PROCEDURE update_salary COMPILE;

Manually Recompiling Packages

To recompile either a package body or both a package specification and body, use the ALTER PACKAGE command with the COMPILE parameter. For example, the following SQL*Plus statements recompile just the body and the body and specification of the package ACCT_MGMT_PACKAGE, respectively:

SQLPLUS> ALTER PACKAGE acct_mgmt_package COMPILE BODY;

SQLPLUS> ALTER PACKAGE acct_mgmt_package COMPILE PACKAGE;

All packages, procedures, and functions can be recompiled using the following syntax. The objects are compiled in dependency order, enabling each to be compiled only once.

SQLPLUS> EXECUTE DBMS_UTILITY.COMPILE_ALL;

Privileges Required to Recompile a Procedure or Package

You can manually recompile a procedure or package only if it is contained in your schema and you have the ALTER ANY PROCEDURE system privilege.

Manually Recompiling Triggers

An existing trigger, enabled or disabled, can be manually recompiled using the ALTER TRIGGER command. For example, to force the compilation of the trigger named REORDER, enter the following statement:

ALTER TRIGGER reorder COMPILE;

Privileges Required to Recompile a Trigger

To recompile a trigger, you must own the trigger or have the ALTER ANY TRIGGER system privilege.