CREATE VIEW
Purpose
Use this statement to create a view.
Prerequisites
-
You need either the system privilege
USE ANY SCHEMA
or the object privilegeUSAGE
on the target schema, or the schema must be owned by you or one of your assigned roles. - You need the system privilege
CREATE ANY VIEW
, orCREATE VIEW
if the view is created in a schema owned by you or one of your assigned roles. - The owner of the view (who is not automatically the creator) must possess the corresponding
SELECT
privilege on all objects that are referenced in the subquery. - If the
OR REPLACE
option is specified and the view already exists, then the user also needs the same rights as forDROP VIEW
.
Syntax
create_view::=
Usage Notes
- An already existing view can be replaced with the
OR REPLACE
option, without having to explicitly delete the view with the DROP VIEW statement. Keep in mind that existing object privileges on the view are dropped when replacing a view. - By specifying the
FORCE
option, you can create a view without compiling its text. This can be very useful if you want to create many views which depend on each other and which otherwise must be created in a certain order. Note that syntax or other errors will not be found until the view is first used. - By specifying the column aliases, you can define the column names of the view. If the column aliases are not specified, then the column names are derived from the subquery. If the columns are not directly referenced in the subquery but assembled in expressions, then you must specify aliases for these either within the subquery or from the entire view.
- The view creation text is limited to 2.000.000 characters.
- A view is set to
INVALID
if one of its underlying objects was changed (see also columnSTATUS
in system tables, for example, EXA_ALL_COLUMNS). At the next read access, the system tries to automatically recompile the view. If this is successful, the view is valid again. Otherwise, an appropriate error message is thrown. This means in particular that an invalid view can still be usable. - The view comment is visible when performing a DESC[RIBE]
FULL
command for the view.