ALTER VIEW

Purpose

Use the ALTER VIEW statement to explicitly recompile a view that is invalid. Explicit recompilation lets you locate recompilation errors before run time. You may want to recompile a view explicitly after altering one of its base tables to ensure that the alteration does not affect the view or other objects that depend on it.

You can also use ALTER VIEW to define, modify, or drop view constraints.

When you issue an ALTER VIEW statement, Oracle recompiles the view regardless of whether it is valid or invalid. Oracle also invalidates any local objects that depend on the view.

Notes: This statement does not change the definition of an existing view. To redefine a view, you must use CREATE VIEW with the OR REPLACE keywords. If you alter a view that is referenced by one or more materialized views, those materialized views are invalidated. Invalid materialized views cannot be used by query rewrite and cannot be refreshed.

See Also: CREATE VIEW for information on redefining a view ALTER MATERIALIZED VIEW for information on revalidating an invalid materialized view Oracle9i Data Warehousing Guide for general information on data warehouses Oracle9i Database Concepts for more about dependencies among schema objects

Prerequisites

The view must be in your own schema or you must have ALTER ANY TABLE system privilege.

Syntax

alter_view::=

Text description of alter_view

table_or_view_constraint: See constraint_clause.

Keywords and Parameters

schema

Specify the schema containing the view. If you omit schema, Oracle assumes the view is in your own schema.

view

Specify the name of the view to be recompiled.

ADD Clause

Use the ADD clause to add a constraint to view.

See Also: constraint_clause for information on view constraints and their restrictions

MODIFY CONSTRAINT Clause

Use the MODIFY CONSTRAINT clause to change the RELY or NORELY setting of an existing view constraint.

Restriction: You cannot change the setting of a unique or primary key constraint if it is part of a referential integrity constraint without dropping the foreign key or changing its setting to match that of view.

See Also: RELY | NORELY for information on the uses of the RELY and NORELY settings

DROP Clause

Use the DROP clause to drop an existing view constraint.

Restriction: You cannot drop a unique or primary key constraint if it is part of a referential integrity constraint on a view.

COMPILE

The COMPILE keyword is required. It directs Oracle to recompile the view.

Example

ALTER VIEW example

To recompile the view customer_view, issue the following statement:

ALTER VIEW bombay_inventory
    COMPILE; 

If Oracle encounters no compilation errors while recompiling customer_view, customer_view becomes valid. If recompiling results in compilation errors, Oracle returns an error and customer_view remains invalid.

Oracle also invalidates all dependent objects. These objects include any procedures, functions, package bodies, and views that reference customer_view. If you subsequently reference one of these objects without first explicitly recompiling it, Oracle recompiles it implicitly at run time.