Replaces a view definition with a modified version.
Syntax
Syntax 1 – Alter the structure of the view
ALTER VIEW
… [ owner.]view-name [ ( column-name [ , … ] ) ]
… AS select-statement
… [ WITH CHECK OPTION ]
Syntax 2 – Change attributes for the view
ALTER VIEW
… [ owner.]view-name
… { SET HIDDEN | RECOMPILE | DISABLE | ENABLE }
Usage
- AS clause – purpose and syntax identical to CREATE VIEW statement.
See CREATE VIEW Statement.
- WITH CHECK OPTION clause – purpose and syntax identical to CREATE VIEW statement.
- RECOMPILE clause – recreate the column definitions for the view. Identical in functionality to
the ENABLE clause, except you can use it on a view that
is not disabled.
- DISABLE clause – disable the view from use by the database server.
- ENABLE clause – enable a disabled view, which causes the database server to recreate the
column definitions for the view. Before you enable a view, you must enable
any views on which it depends.
When you alter a view, existing permissions on the view are
maintained and do not require reassignment. Instead of using the ALTER VIEW statement, you could also drop the view and
recreate it using DROP VIEW and CREATE VIEW, respectively. If you do this, view
permissions must be reassigned.
After completing the view alteration using Syntax 1, the database
server recompiles the view. Depending on the type of change you made, if there are
dependent views, the database server attempts to recompile them. If you made changes
that impact a dependent view, that view may become invalid, requiring you to alter
the definition for the dependent view.
Warning!
If the SELECT statement
defining the view contains an asterisk (*), the number of the columns in the
view could change if columns were added or deleted from the underlying tables.
The names and data types of the view columns could also change.
- Syntax 1 – alter the structure of the view. Unlike altering tables, where your change
might be limited to individual columns, altering the structure of a view
requires that you replace the entire view definition with a new definition,
much as you would when creating the view using the CREATE
VIEW statement.
- Syntax 2 – change attributes for the view, such as whether the view definition is
hidden.
When you use SET HIDDEN, you can
unload and reload the view into other databases. Debugging using the debugger does
not show the view definition, nor is it available through procedure profiling. If
you need to change the definition of a hidden view, you must drop the view and
create it again using the CREATE VIEW statement.
When you use the DISABLE clause,
the view is no longer available for use by the database server to answer queries.
Disabling a view is similar to dropping one, except that the view definition remains
in the database. Disabling a view also disables any dependent views. Therefore, the
DISABLE clause requires exclusive access, not
only to the view being disabled, but to any dependent views, which are also
disabled.
Side effects:
-
Automatic commit
- All procedures and triggers are unloaded from memory, so
that any procedure or trigger that references the view reflects the new view
definition. The unloading and loading of procedures and triggers can have a
performance impact if you regularly alter views.
Permissions
RECOMPILE or
ENABLE
clause – For view requires one of:
- ALTER ANY VIEW system privilege.
- ALTER ANY OBJECT system privilege.
- You own the view.
- Also require one of:
- SELECT ANY TABLE system privilege.
- SELECT privilege on the underlying tables of the view.
For materialized view requires one of:
- ALTER ANY MATERIALNESS VIEW system privilege.
- ALTER ANY OBJECT system privilege.
- You own the materialized view.
- Also require one of:
- SELECT ANY TABLE system privilege.
- SELECT privilege on the underlying tables of the materialized
view.
DISABLE clause – For view requires one of:
- ALTER ANY VIEW system privilege.
- ALTER ANY OBJECT system privilege.
- You own the view.
For materialized view requires one of:
- ALTER ANY MATERIALIZED VIEW system privilege.
- ALTER ANY OBJECT system privilege.
- You own the materialized view.
All other clauses require one of:
- ALTER ANY OBJECT system privilege.
- You own the view.