Oracle SQL: the Essential Reference

CREATE/ALTER/DROP VIEW

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [schema.]viewname [(alias[,alias ...])] AS viewquery [WITH CHECK OPTION [CONSTRAINT constraint]]

Creates a view.

ALTER VIEW [schema.]viewname COMPILE

Recompiles a view.

DROP VIEW [schema.]view_name

Removes a view from the database.

Keywords

OR REPLACE

Specifies that the view be replaced if it already exists.

FORCE

Specifies that the view be created regardless of whether the view's base tables exist, or whether the owner of the schema has privileges on them.

NOFORCE

Specifies that the view be created only if the base tables exist and the owner of the schema has privileges on them. This option is the default.

viewname

Specifies the name of the view to be created.

alias

One or more aliases that correspond to columns or expressions returned by the view's query.

viewquery

Any SQL SELECT statement. See Chapter 3 for the syntax of the SELECT statement.

WITH CHECK OPTION

Specifies that inserts and updates performed through the view must result in rows that the view query can select.

CONSTRAINT

Specifies a name for the CHECK OPTION constraint. The default is a system-assigned name in the form SYS_Cn, for which n is an integer resulting in a unique name.

COMPILE

This keyword is required when altering a view and causes the view to be recompiled.

The ALTER VIEW statement explicitly recompiles a view, and its use is recommended ...