Creates a view with the specified name on existing tables and/or views.
Syntax
ALTER VIEW [ owner_name. ] view_name
[ ( column_name, column_name, ... ) ]
AS [ ( ] query_expression [ ) ]
[ WITH CHECK OPTION ] ;
Notes
ALTER VIEW has the same syntax as CREATE VIEW. ALTER VIEW does not modify an existing view, but rather replaces the existing view with a complete new definition. However, it retains the permissions from the existing view for the new view. It is best practice, therefore, to use a script file to create a view that may be modified in the future. The script can then be modified and used with ALTER VIEW to effect the change without needing to reenter the permissions of the view.
The owner_name is made the owner of the created view.
The column names specified for the view are optional and provide an alias for the columns selected by the query specification. If the column names are not specified then the view will be created with the same column names as the tables and/or views it is based on.
A view is deletable if deleting rows from that view is allowed. For a view to be deletable, the view definition has to satisfy the following conditions:
The first FROM clause contains only one table reference or one view reference.
There are no aggregate functions, DISTINCT clause, GROUP BY or HAVING clause in the view definition.
If the first FROM clause contains a view reference, then the view referred to is deletable.
A view is updatable if updating rows from that view is allowed. For a view to be updatable, the view has to satisfy the following conditions:
The view is deletable (That is, it satisfies all the conditions specified above for deletability).
All the select expressions in the first SELECT clause of the view definition are simple column references.
If the first FROM clause contains a view reference, then the view referred to is updatable.
A view is insertable if inserting rows into that view is allowed. For a view to be insertable, the view has to satisfy the following conditions:
The view is updatable (That is, it satisfies all the conditions specified above for updatability).
If the first FROM clause contains a table reference, then all NOT NULL columns of the table are selected in the first SELECT clause of the view definition.
If the first FROM clause contains a view reference, then the view referred to is insertable.
The WITH CHECK OPTION clause can be specified only if the view is updatable.
If WITH CHECK OPTION clause is specified when defining a view, then during any update or insert of a row on this view, it is checked that the updated/inserted row satisfies the view definition (That is, the row is selectable using the view).
Examples
ALTER VIEW ne_customers AS
SELECT cust_no, name, street, city, state, zip
FROM customer
WHERE state IN ('NH', 'MA', 'NY', 'VT')
WITH CHECK OPTION ;
ALTER VIEW order_count (cust_number, norders) AS
SELECT cust_no, COUNT(*)
FROM orders
GROUP BY cust_no;
Authorization
The user executing this statement must have the following privileges:
DBA or RESOURCE privilege.
SELECT privilege on all the tables/views referred to in the view definition.
If owner_name is specified and is different from the name of the user executing the statement, then the user must have DBA privilege.
