Oracle Views

A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.

Views are known as logical tables. They represent the data of one or more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also.

Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can Query, Insert, Update and delete from views, just as any other table.

Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.

The following sections explain how to create, replace, and drop views using SQL commands.

Create View

Use the CREATE VIEW statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.

OR REPLACE

Specify "OR REPLACE" to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it.

Create or Replace a View

SQL>  create or replace view view_t as
  2      select id view_id, data view_data
  3        from t;

View created.

Create view without indiating the column name

SQL> CREATE VIEW EmployeeView AS
  2   select First_Name, Last_Name from Employee;

View created.

Create OR REPLACE VIEW from selected columns in a table

SQL> CREATE OR REPLACE VIEW myView (First_Name, Last_Name) AS
  2      select First_Name,
  3             Last_Name
  4      from Employee
  5      where Salary > 2000;

View created.

Create Force View

SQL>  create view invalid_view as
  2      select * from table_that_does_not_exist;
    select * from table_that_does_not_exist
                  *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL>  create force view invalid_view as
  2      select * from table_that_does_not_exist;

Warning: View created with compilation errors.

Create Materialized View

SQL>  create materialized view my_orders_mv
  2      build immediate
  3    refresh on commit
  4     enable query rewrite
  5    as
  6      select employee_id,
  7             count(*) total_orders
  8        from employees
  9       group by employee_id
 10    /
      from employees
           *
ERROR at line 8:
ORA-00439: feature not enabled: Materialized view rewrite

SQL>  set timing on
SQL>  select * from my_orders_mv;
 select * from my_orders_mv
               *
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.07
SQL>  set timing off

6. Create a view with check option

SQL>  create or replace view department_10 as
  2      select *
  3        from employees
  4       where department_id = 10
  5        with check option;

View created.

Oracle Alter View

Use the ALTER VIEW statement to explicitly recompile a view that is invalid or to modify view constraints. Explicit recompilation lets you locate recompilation errors before run time. You may want to recompile a view explicitly after altering one of its base tables to ensure that the alteration does not affect the view or other objects that depend on it.

You can also use ALTER VIEW to define, modify, or drop view constraints.

This statement does not change the definition of an existing view. To redefine a view, you must use CREATE VIEW with the OR REPLACE keywords.

When you issue an ALTER VIEW statement, Oracle Database recompiles the view regardless of whether it is valid or invalid. The database also invalidates any local objects that depend on the view.

If you alter a view that is referenced by one or more materialized views, then those materialized views are invalidated. Invalid materialized views cannot be used by query rewrite and cannot be refreshed.

Alter view to recompile

SQL>  alter view view_t compile;

View altered.

The COMPILE keyword directs Oracle Database to recompile the view.

Dropping Views

Use the SQL command DROP VIEW to drop a view. For example:

DROP VIEW View_Name;