Oracle 10g Tutorials - Creating Views, Altering, Dropping and Replacing Views

To replace a view, you must have all the privileges required to drop and create a view. If the definition of a view must change, the view must be replaced; you cannot use an ALTER VIEW statement to change the definition of a view. You can replace views in the following ways:

You can drop and re-create the view.

Caution:

When a view is dropped, all grants of corresponding object privileges are revoked from roles and users. After the view is re-created, privileges must be regranted.

You can redefine the view with a CREATE VIEW statement that contains the OR REPLACE clause. The OR REPLACE clause replaces the current definition of a view and preserves the current security authorizations. For example, assume that you created the sales_staff view as shown earlier, and, in addition, you granted several object privileges to roles and other users. However, now you need to redefine the sales_staff view to change the department number specified in the WHERE clause. You can replace the current version of the sales_staff view with the following statement:

CREATE OR REPLACE VIEW sales_staff AS
SELECT empno, ename, deptno
FROM emp
WHERE deptno = 30
WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

The image cannot be displayed, because it contains errors.

Figure 16. Creating views with force option and replacing views

Before replacing a view, consider the following effects:

Replacing a view replaces the view definition in the data dictionary. All underlying objects referenced by the view are not affected.

If a constraint in the CHECK OPTION was previously defined but not included in the new view definition, the constraint is dropped.

All views and PL/SQL program units dependent on a replaced view become invalid (not usable).

Replacing View

Tags