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 How to create view in oracle,Oracle create view example,how to alter view in oracle,How to compile view in oracle,How to drop view in oracle,force view in oracle,How to check view definition in oracle .You will come to know why Oracle 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?
-A Oracle view is a representation of data from one or more oracle 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 a 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
How to Modify the Oracle View/how to alter view in oracle
We can modify the views using create or replace view command. This allows altering view without dropping, recreating, and re-granting object privileges. There is no such command as alter view add a column or alter view modify the column. We need to go through the create or replace view method
Create or replace view emp_high_sal as select empno,ename,sal From emp where sal > 200000;
This command creates the view if it is not existent and modifies the view if it is existent. All the plsql units which are accessing the view will become invalid after the view modification
How to compile view in oracle
Recompiling a view is done using the
ALTER VIEW <view_name> COMPILE;
Alter view emp_high_sal compile;
How to drop view in oracle
Dropping a view is done using the DROP VIEW command.
Dropping a view has no effect on the base tables on which the 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 ;
Creating Force Oracle Views/force view in oracle
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.
Let’s take an example to understand it. We don’t have a table like TEST. But we can still create the view on it before creating the table using the force Option. The view will be created with a compilation error and will remain invalid
Create force view TEST_V as select col1,col2,col3 From test where col1 > 1000;
Now create the table
create table TEST ( col1 number, col2 number, col3 number);
Now you can compile the view and it will become valid
alter view TEST_V compile;
What happens to the view if the base tables are dropped?
If the underlying tables are dropped, then oracle does not drop the view automatically. It remains in the database and it will be in an invalid state. If the underlying tables are recreated, they 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 contains information about the objects owned by the user only For Example USER_TABLES,USER_TAB_COLS |
| ALL-% | This view contains information on the objects which the user can access in the database. Example ALL_TABLES,ALL_TAB_COLS |
| DBA_% | This view contains information about all objects in the system and these are restricted views that are accessible to the user who has a DBA role Example DBA_TABLES |
| DBA_% views about view information | ALL_% views about view information | USER_% views about view information | |
| The column which can be updated | DBA_UPDATABLE_COLUMNS | ALL_UPDATABLE_COLUMNS | USER_UPDATABLE_COLUMNS |
| 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 check view definition in oracle
Query the TEXT column of table DBA_VIEWS.
Syntax: SQL> set long 10000 SQL> select TEXT 2 FROM DBA_VIEWS 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
Syntax:
SQL> set long 1000
SQL> set pagesize 0
SQL>select DBMS_METADATA.GET_DDL('VIEW','<view_name>') from DUAL
Hope you like a detailed article on Oracle view. I have tried to answer the popular queries on the view such as How to create view in oracle, Oracle create view example, how to alter view in oracle, How to compile view in oracle, How to drop view in oracle, force view in oracle, How to check view definition in oracle
Related Articles to read
oracle create a table: Tables are the basic unit of data storage in an Oracle Database. we cover how to use Oracle create table command to create a table with a foreign key /primary key
external tables in Oracle: Check out this post for information on the usage of the external table in oracle with an example, how to create an external table, how to use it
Sequences in oracle: What is Sequence in oracle, Description of all options, How to create them, Oracle 12c new feature of sequences, How to drop and alter them
Indexes in oracle: Check out Oracle indexes information, different types of indexes in oracle with an example, how to create/drop/alter the index in oracle
Oracle Materialized Views : what is Oracle materialized view, Oracle Materialized View Log, Refresh Group, Difference between view and materialized view
How to monitor the progress of refresh of Materialized views
oracle alter table modify column type: Check out this post on how to modify columns in oracle using the oracle alter table modify the column. Modify column can be changing default value, allowing not null
update the view in Oracle : check out this post to update the view in Oracle, insert into view oracle, delete from view in Oracle database
Top-N Queries in Oracle : Check out this page on exploring the various ways to achieve Top-N Queries in Oracle like oracle top 100, selecting top 10 rows in oracle
Auto Increment in Oracle : Check out how to use Auto Increment in Oracle before 12c version and with 12 and above version with detailed examples
Recommended Courses
Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning
