Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Managing Object Dependencies

This section describes the various object dependencies, and includes the following topics:

First, review Table 16 - 1, which shows how objects are affected by changes in other objects on which they depend.

Operation Resulting Status of Object Resulting Status of Dependent Objects
CREATE table, sequence, synonym VALID if there are no errors No change1
ALTER table (ADD column MODIFY column) RENAME table, sequence, synonym, view VALID if there no errors INVALID
DROP table, sequence, synonym, view, procedure, function, package None; the object is dropped INVALID
CREATE view, procedure2 VALID if there are no errors; INVALID if there are syntax or authorization errors No change1
CREATE OR REPLACE view or procedure2 VALID if there are no error; INVALID if there are syntax or authorization errors INVALID
REVOKE object privilege3 ON object TO/FROM user No change All objects of user that depend on object are INVALID3
REVOKE object privilege3 ON object TO/FROM PUBLIC No change All objects in the database that depend on object are INVALID3
REVOKE system privilege4 TO/FROM user No change All objects of user are INVALID4
REVOKE system privilege4 TO/FROM PUBLIC No change All objects in the database are INVALID4
Table 16 - 1. Operations that Affect Object Status

1 May cause dependent objects to be made INVALID, if object did not exist earlier. 2 Stand-alone procedures and functions, packages, and triggers. 3 Only DML object privileges, including SELECT, INSERT, UPDATE, DELETE, and EXECUTE; revalidation does not require recompiling. 4 Only DML system privileges, including SELECT, INSERT, UPDATE, DELETE ANY TABLE, and EXECUTE ANY PROCEDURE; revalidation does not require recompiling.

Oracle automatically recompiles an invalid view or PL/SQL program unit the next time it is used. In addition, a user can force Oracle to recompile a view or program unit using the appropriate SQL command with the COMPILE parameter. Forced compilations are most often used to test for errors when a dependent view or program unit is invalid, but is not currently being used. In these cases, automatic recompilation would not otherwise occur until the view or program unit was executed. To identify invalid dependent objects, query the views USER_/ALL_/DBA_OBJECTS.

Manually Recompiling Views

To recompile a view manually, the view must be contained in your schema or you must have the ALTER ANY TABLE system privilege. Use the ALTER VIEW command with the COMPILE parameter to recompile a view. The following statement recompiles the view EMP_DEPT contained in your schema:

ALTER VIEW emp_dept COMPILE;

Manually Recompiling Procedures and Functions

To recompile a procedure manually, the procedure must be contained in your schema, or you must have the ALTER ANY PROCEDURE system privilege. Use the ALTER PROCEDURE/FUNCTION command with the COMPILE parameter to recompile a stand-alone procedure or function. The following statement recompiles the stored procedure UPDATE_SALARY contained in your schema:

ALTER PROCEDURE update_salary COMPILE;

Manually Recompiling Packages

To recompile a package manually, the package must be contained in your schema, or you must have the ALTER ANY PROCEDURE system privilege. Use the ALTER PACKAGE command with the COMPILE parameter to recompile either a package body or both a package specification and body. The following statements recompile just the body, and the body and specification of the package ACCT_MGMT, respectively:

ALTER PACKAGE acct_mgmt COMPILE BODY;
ALTER PACKAGE acct_mgmt COMPILE PACKAGE;

Contents Index Home Previous Next