Oracle SQL - View View Creation

  1. HOME
  2. Oracle SQL
  3. View
  4. View Creation

Introduction

You can create views with the CREATE VIEW command.

The OR REPLACE option allows you to replace an existing view definition.

View privileges are not retained when you use the DROP VIEW / CREATE VIEW command sequence.

CREATE OR REPLACE VIEW command does preserve them.

The FORCE option doesn't check whether the underlying base tables exist or whether you have sufficient privileges to access those base tables.

These conditions must eventually be met when using your view definition.

Normally, views inherit their column names from the defining query.

Views have the same column naming rules and constraints as regular tables:

  • column names must be different, and
  • they cannot contain characters such as brackets and arithmetic operators.

You can specify column aliases in the SELECT clause of the defining query.

You can specify explicit column aliases in the CREATE VIEW command between the view name and the AS clause.

The WITH CHECK OPTION and WITH READ ONLY options influence view behavior under data manipulation activity.

The following code shows two very similar SQL statements: the first statement creates a view, and the second statement creates a table. 

create view dept20_v as
select * from emp where deptno = 20;

create table dept20_t as
select * from emp where deptno = 20;

The contents of the view DEPT20_V will be dependent on the EMPLOYEES table.

The table DEPT20_T uses the current EMPLOYEES table as only a starting point. It is an independent table with its own contents.

Related Topics