Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Purpose
Use the ALTER
VIEW
statement to explicitly recompile a view that is invalid or to modify view constraints. 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.
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.
When you issue an ALTER
VIEW
statement, Oracle Database recompiles the view regardless of whether it is valid or invalid. The database also invalidates any local objects that depend on the view.
If you alter a view that is referenced by one or more materialized views, then 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 and ALTER MATERIALIZED VIEW for information on revalidating an invalid materialized view
Oracle Data Warehousing Guide for general information on data warehouses
Oracle 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::=
(out_of_line_constraint::=--part of constraint::= syntax)
Semantics
schema
Specify the schema containing the view. If you omit schema
, then Oracle Database assumes the view is in your own schema.
view
Specify the name of the view to be recompiled.
Use the ADD
clause to add a constraint to view
. Please refer to constraint for information on view constraints and their restrictions.
Use the MODIFY
CONSTRAINT
clause to change the RELY
or NORELY
setting of an existing view constraint. Please refer to "RELY Clause" for information on the uses of these settings and to "Notes on View Constraints" for general information on view constraints.
Restriction on Modifying Constraints 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
.
Use the DROP
clause to drop an existing view constraint.
Restriction on Dropping Constraints You cannot drop a unique or primary key constraint if it is part of a referential integrity constraint on a view.
The COMPILE
keyword directs Oracle Database to recompile the view.
Example
Altering a View: Example To recompile the view customer_ro
(created in "Creating a Read-Only View: Example"), issue the following statement:
ALTER VIEW customer_ro COMPILE;
If Oracle Database encounters no compilation errors while recompiling customer_ro
, then customer_ro
becomes valid. If recompiling results in compilation errors, then the database returns an error and customer_ro
remains invalid.
Oracle Database also invalidates all dependent objects. These objects include any procedures, functions, package bodies, and views that reference customer_ro
. If you subsequently reference one of these objects without first explicitly recompiling it, then the database recompiles it implicitly at run time.