|Oracle® Database SQL Language Reference
11g Release 2 (11.2)
Part Number E17118-03
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
VIEW to define, modify, or drop view constraints.
You cannot use this statement to change the definition of an existing view. Further, if DDL changes to the view's base tables invalidate the view, then you cannot use this statement to compile the invalid view. In these cases, you must redefine the view using
VIEW with the
When you issue an
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.
The view must be in your own schema or you must have
TABLE system privilege.
Specify the schema containing the view. If you omit
schema, then Oracle Database assumes the view is in your own schema.
Specify the name of the view to be recompiled.
ADD clause to add a constraint to
view. Refer to constraint for information on view constraints and their restrictions.
CONSTRAINT clause to change the
NORELY setting of an existing view constraint. 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
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.
COMPILE keyword directs Oracle Database to recompile the view.
These clauses are valid only for editioning views.
ONLY to indicate that the editioning view cannot be updated.
WRITE to return a read-only editioning view to read/write status.
When you specify these clauses, the database does not invalidate dependant objects, but it may invalidate cursors.
See Also:CREATE VIEW for information about editioning views
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 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.