Complete Guide on Oracle views (Sql Views)

Last updated on February 24th, 2019 at 05:24 am

In this post,I would be talking about Oracle views which is used quite heavily in various application for security and complexity reason.I would be listing complete life cycle of Oracle view i.e creation,modification,maintenance, drop .You will come to know why Sql View are important and what all can be done with it.

This same stuff is almost valid for sql view also

What is Oracle  view?

oracle view

-A Oracle view is a representation of data from one or more tables or views.

-A Oracle view is a named and validated SQL query which is stored in the Oracle data dictionary.

Views do not contain any data – it is just a stored query in the database that can be executed when called. All the data it shows comes from the base tables.  One can think of a view as a virtual table or mapping of data from one or more

-A view takes up no storage space other than for the definition of the view in the data dictionary.

  • A view can be used to represent a subset of data (for security issues), used to represent a superset of data (join several tables to one view), to hide complex joins, to provide meaningful names for columns and to minimize application and data source changes.

-The referenced tables are called base tables.

  • In order to create view use the CREATE VIEW command followed by a SQL query.

Oracle create view example

CREATE VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];

-You can specify the column names using

 CREATE VIEW (col1, col2…) AS SELECT COL1, COL2… FROM …;

-Creating a view using CREATE FORCE VIEW will create the view with compilation errors.

-You can create a read-only view using the WITH READ ONLY clause.

-Oracle 9i allows creating constraints on views.

-Constraints on views are not enforced, they are declarative constraints. To enforce constraints you must create the constraints on the base tables. You must always include the DISABLE NOVALIDATE clause when creating constraints on views.


Privileges Required to create oracle view

To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user’s schema, you must have the CREATE ANY VIEW system privilege.


Example of Oracle Views creation

View Creation
Create view emp_data  as select e.empno,e.ename,e.sal,e.deptno,d.dname
From emp e, dept d
where e.deptno=d.deptno;Create view emp_high_sal  as select empno,ename,sal
From emp  where sal > 100000;
Describe the views
Desc Emp_data
Desc emp_high_sal
Select the data from view
Select * from emp_data;
select * from emp_high_sal

creating emp and dept table for Oracle views creation example

complete guide on Oracle views (Sql views) : View creation example

complete guide on Oracle views (Sql views) : Describe views

complete guide on Oracle views (Sql views): Select from views

How to Modify the Oracle View

We can modify the views using create or replace view command. This allows altering view without dropping, recreating and re-granting object privileges.

Create or replace view emp_high_sal  as select empno,ename,sal
From emp  where sal > 200000;

This command create the view if it is not existent and modify the view if it is existent. All the plsql units which are accessing the view will become invalid after the view modification

How to modify view using Create or replace views statement


How to recompile the Oracle view

Recompiling a view is done using the


Alter view  emp_high_sal  compile;

How to drop the Oracle view

Dropping a view is done using DROP VIEW command.

Dropping a view has no effect on the base tables on which view is based. Users who were accessing the data of the base tables using view will no longer be able to access the data of the base tables.

DROP VIEW view_name;

Drop view  emp_high_sal  ;


How to perform DML operations on Oracle View

-We can perform DML operations like insert,delete and update on the view also with some restrictions

-A VIEW in Oracle is created by joining one or more tables. When you update record(s) in a VIEW, it updates the records in the underlying tables that make up the View.

-But off course you need the appropriate privileges for the underlying tables

-DML operations on views can be performed as long as the view does not contain these words: DISTINCT, GROUP BY, START WITH, CONNECT BY, ROWNUM, UNION [ALL], INTERSECT, MINUS or view has subqueries.

-When using the WITH CHECK OPTION WITH CHECK OPTION [CONSTRAINT <constraint_name>] clause, one can restrict the values inserted using the views:

For exampleLets check our previously created view

Create view emp_data  as select e.empno,e.ename,e.sal,e.deptno,d.dname
From emp e, dept d
where e.deptno=d.deptno;

In order to view allowable DML operations on a view, query the USER_UPDATABLE_COLUMNS view.

select table_name,column_name,updatable,insertable,deletable from USER_UPDATABLE_COLUMNS  where table_name=’EMP_DATA’;

complete guide on Oracle views (Sql views) :User_updatable_columns

Lets try to insert

complete guide on Oracle views (Sql views): insert into views

Now take an example with check option


 insert into views with check option

One can use DML operation on this view only on values smaller than 2000 only

-When specifying the constraint name, Oracle will create the constraint using specified name (otherwise will create using SYS_C followed by a unique string).


Creating Force Oracle Views

A view can be created even if the defining query of the view cannot be executed, using the CREATE FORCE VIEW command. Such a view is called a view with errors. This option can be useful for import/installation tools to create a view before the underlying objects are present.


What happens to view if the base tables are dropped?


If the underlying tables are dropped, then oracle does not dropped the view automatically. It remains in the database and it will be in invalid state.  If the underlying tables are recreated, it will become valid again

Dictionary Views for seeing the Oracle View data

View details can be queried from the dictionary by querying either USER_VIEWS, ALL_VIEWS or DBA_VIEWS. Views are useful for security and information hiding, but can cause problems if nested too deeply. Some of the advantages of using views:

  • Reduce the complexity of SQL statements
  • Share only specific rows in a table with other users
  • Hide the NAME and OWNER of the base table
  • There are three categories of  views
USER_% This view contain information of the objects owned by the user only



ALL-% This view contains information of the objects which the user can access in the database.



DBA_% This view contain information of the all objects   in the system and these are restricted views which are accessible to the user who have DBA role



  DBA_% views about view information ALL_% views about  view information USER_% views about view information
View about view information dba_views all_views user_views


Listing all views in Oracle

To list all views owned by the current user
select  view_name from user_views;

To list all views in a database:

Select  owner,view_name from dba_views;

To list views accessible to the current user:

select view_name from all_views;

To describe the view in sqlplus
desc <view_name>

How to determine the query of the already created view

Query the TEXT column of table DBA_VIEWS.

SQL> set long 10000

SQL> select TEXT
3  where OWNER = '<owner_name>'
4  and VIEW_NAME  = '<view_name>';

How to extract the view definition (DDL statements) from an Oracle database without having to go through a stack of dictionary views

SQL> set long 1000
SQL> set pagesize 0select DBMS_METADATA.GET_DDL('VIEW','<view_name>') from DUAL

Related Articles to read

Oracle documentation on Views

how to create table in oracle

How to use external tables in Oracle with example

What is Sequences in oracle

Oracle Indexes and types of indexes in oracle with example

What are Oracle Materialized Views

How to monitor the progress of refresh of Materialized views

Best Selling Udemy Courses

Oracle DBA 11g/12c – Database Administration for Junior DBA
Oracle Database 12c SQL Certified Associate 1Z0-071
Learning Oracle 12c – A Beginners Oracle Training Video

Leave a Reply