The common definition of the "view" term is "a result set of a stored query that is accessed as a virtual table". In this basic tutorial we will demonstrate how to create views in Oracle Database and how to use a view for data replication.
What are database views
Let's have two sample tables. The first one is called DEPARTMENTS and it contains a list of all departments in a given company. The second one is called EMPLOYEES and it contains a list of all employees of the company. The tables are created and filled with some test data. Their DDL's look like this:
CREATE TABLE DEPARTMENTS (
ID NUMBER NOT NULL,
DEPARTMENT_NAME VARCHAR2(20),
CONSTRAINT DEPARTMENTS_PK PRIMARY KEY (ID) ENABLE
);
CREATE TABLE EMPLOYEES (
ID NUMBER NOT NULL,
FIRST VARCHAR2(20),
LAST VARCHAR2(20),
DEPARTMENT NUMBER,
CONSTRAINT EMPLOYEES_PK PRIMARY KEY (ID) ENABLE,
CONSTRAINT EMPLOYEES_DEPARTMENTS_FK FOREIGN KEY (DEPARTMENT)
REFERENCES DEPARTMENTS (ID) ENABLE
);
The EMPLOYEES table has a column named DEPARTMENT that references the ID column of the DEPARTMENTS table. We use this relation to link every single employee to a given company department. The integrity of this relation is ensured by the EMPLOYEES_DEPARTMENTS_FK constraint.
If we want to create a report with all employees and their corresponding department we can execute the following query:
SELECT FIRST, LAST, DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT = D.ID
The query returns the following data:
FIRST LAST DEPARTMENT_NAME -------------------- -------------------- -------------------- Kage Baker Q&A Steven Barnes ACCOUNTING William Barton MARKETING Harry Bates Q&A Ted Chiang R&D Jo Clayton Q&A Hal Clement ACCOUNTING Graham Diamond SALES David Drake MARKETING 9 rows selected
If we have to execute this query regularly, we can create a view based on it. We do this by executing a CREATE VIEW statement.
SQL> CREATE VIEW EMPLOYEE_DEPARTMENTS AS SELECT FIRST, LAST, DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT = D.ID; CREATE VIEW succeeded. SQL>
Selecting all data from the view will return the same data as the original query.
SQL> SELECT * FROM EMPLOYEE_DEPARTMENTS FIRST LAST DEPARTMENT_NAME -------------------- -------------------- -------------------- Kage Baker Q&A Steven Barnes ACCOUNTING William Barton MARKETING Harry Bates Q&A Ted Chiang R&D Jo Clayton Q&A Hal Clement ACCOUNTING Graham Diamond SALES David Drake MARKETING 9 rows selected SQL>
Besides saving us some typing, views are extremely helpful when we have to combine data from multiple tables or we have to grant access rights to specific subset of our data. It is important to keep in mind that views do not contain any data per se. They just store the query that fetches the data from the original tables.
When a user tries to get the view data, the database runs the query that was used to define the view (step 1 on the diagram). The data is pulled from the original tables and presented to the user in step 2.
Materialized views
The main difference between regular and materialized view is that the latter does not query the original tables for every user request. The materialized view holds a copy of their data instead.
When a user runs a CREATE MATERIALIZED VIEW command, the database performs a single run of the query that defines the view. The query results are then stored within the newly created view (step 1 on the diagram). When someone queries the view at a later stage, the database is not looking at the original tables (EMPLOYESS, DEPARTMENTS). The result is pulled directly from the stored data (step 2).
In most cases data can be fetched faster from a materialized view (compared to a regular view). This happens for several reasons. First, the database doesn't need to go through multiple tables. Second, the data that it reads is less (normally the view will expose just a portion of the tables). Third, we can have specific indexes created on the view etc. The price we have to pay for this increased performance is that the view occupies additional space (as it contains a copy of the data from the original tables).
Let's investigate what happens with a materialized view when we change the tables that it's based on. We can create a view in this way:
SQL> CREATE MATERIALIZED VIEW M_EMPLOYEE_DEPARTMENTS AS SELECT FIRST, LAST, DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT = D.ID; CREATE MATERIALIZED succeeded. SQL>
We can see how many rows the database will return if we query the regular view EMPLOYEE_DEPARTMENTS.
SQL> SELECT COUNT(*) FROM EMPLOYEE_DEPARTMENTS COUNT(*) ---------------------- 9 1 rows selected SQL>
Let's to the same for the materialized view.
SQL> SELECT COUNT(*) FROM M_EMPLOYEE_DEPARTMENTS COUNT(*) ---------------------- 9 1 rows selected SQL>
Now let's insert a new record in the EMPLOYEES table.
SQL> INSERT INTO "APP2"."EMPLOYEES" (ID, FIRST, LAST, DEPARTMENT)
VALUES ('10', 'Donald', 'Wandrei', '3');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
Let's count the records in EMPLOYEE_DEPARTMENTS and M_EMPLOYEE_DEPARTMENTS again.
SQL> SELECT COUNT(*) FROM EMPLOYEE_DEPARTMENTS COUNT(*) ---------------------- 10 1 rows selected SQL> SELECT COUNT(*) FROM M_EMPLOYEE_DEPARTMENTS COUNT(*) ---------------------- 9 1 rows selected SQL>
This test confirms that the materialized view is static. It is not effected by changes in the underlying tables, as it presents a copy of the data relevant to the moment of it's creation. The regular view on the other hand reflects all changes as it always has to query the EMPLOYEES and DEPARTMENTS tables.
We will not go into more details about refreshing. It is good to know although that we can setup a materialized view to reflect changes in the original tables. In order to do this we can use the REFRESH clause. We will get back to this later.
Data replication via materialized views
A nice feature provided with views is that the original tables can reside in another database. This database can even be placed on a remote physical machine. If we take advantage of this feature with a materialized view then we can effectively maintain a local copy of any remote data.
We will use a simple demonstration scenario to show how one can configure such replication. Let's have an application APP1 that stores user credentials in a database table called USERS. This table resides in a database called EL5.
Suppose we have to install another application called APP2. It will eventually be used by the same users as APP1, but it will be connected to another database (residing on a host called EL6).
The easiest way to keep the user credential synchronized is to make EL5 visible to EL6 over a database link. Then we can be build a materialized view based on the USERS table. The view will be created inside El6, but it will fetch it's data from EL5.
We could of course build a regular view instead. The advantages that we have with the materialized one is that the access to the credentials will be faster for APP2, if the data is stored locally. Having the data in EL6 also guarantees that the application will keep running even if the connection between EL6 and EL5 goes down, or if the EL5 database is shut down for maintenance.
We will start by creating the USERS table and add some sample records inside.
SQL> CONNECT SYSTEM/*********@EL5_ORCL Connected. SQL> CREATE USER APP1 IDENTIFIED BY APP1; User created. SQL> GRANT CONNECT,RESOURCE TO APP1; Grant succeeded. SQL> CONNECT APP1/APP1@EL5_ORCL Connected. SQL> CREATE TABLE USERS (USER_ID INTEGER PRIMARY KEY, FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), PASSWORD VARCHAR2(20)); Table created. SQL> INSERT INTO USERS VALUES (1, 'BRIAN', 'ALDISS', 'PASS1') 1 row created. SQL> INSERT INTO USERS VALUES (2, 'POUL', 'ANDERSON', 'PASS2') 1 row created. SQL> INSERT INTO USERS VALUES (3, 'NEAL', 'ASHER', 'PASS3') 1 row created. SQL> COMMIT Commit complete. SQL>
Now we move to the other machine - EL6. First we have to create a DATABASE LINK between the two database. We start by editing the TNSNAMES.ORA file and adding a record for EL5 there.
We will use EL5 as a local name. In my case the database runs on 192.168.10.101:1522 and it's SID is ORCL. The record that I have to add looks like this:
EL5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.101)
(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
After saving the TNSNAMES.ORA we can confirm that the database is reachable by issuing a "tnsping el5" command or by trying to connect to it via SQL*Plus (sqlplus app1/app1@el5).
Our next step is to connect to the local database (EL6) and create the user account for the APP2 application.
$ sqlplus system@el6 SQL*Plus: Release 10.2.0.1.0 - Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> CREATE USER APP2 IDENTIFIED BY APP2 User created. SQL> GRANT CONNECT, RESOURCE, CREATE MATERIALIZED VIEW, CREATE DATABASE LINK TO APP2 Grant succeeded. SQL>
The APP2 user has the rights to create database links and materialized views. Let's start by creating the EL5 link. We will define it in such way that the access to the EL5 database will be performed as the APP1 user.
SQL> CONNECT APP2@EL6 Enter password: Connected. SQL> CREATE DATABASE LINK EL5 CONNECT TO APP1 IDENTIFIED BY APP1 USING 'EL5'; Database link created. SQL>
We can try to read the USERS table and confirm that the link is working correctly.
SQL> SELECT COUNT(*) FROM USERS@EL5
COUNT(*)
----------
3
SQL>
Our next step is to build the view, using the EL5 link. We will also use the REFRESH NEXT clause, which will instruct the database to periodically refresh it's data.
SQL> CREATE MATERIALIZED VIEW V_USERS REFRESH NEXT SYSDATE+INTERVAL '10' MINUTE WITH ROWID AS SELECT * FROM USERS@EL5; Materialized view created. SQL>
REFRESH NEXT SYSDATE + INTERVAL '10' MINUTE asks the database to refresh the materialized view every 10 minutes. Let's test the newly created view.
SQL> SELECT COUNT(*) FROM V_USERS
COUNT(*)
----------
3
SQL>
We can confirm that all three rows from the USERS table are copied in the view. What about the synchronization? Let's connect to EL5.
SQL> CONNECT APP1@EL5 Enter password: Connected. SQL>
We insert a new row in the USERS table.
SQL> INSERT INTO USERS VALUES (4, 'ROBERT', 'ASPRIN', 'PASS4') 1 row created. SQL> COMMIT Commit complete. SQL>
We go back to EL6 and run a query that will show us all records together with the system time.
SQL> SELECT TO_CHAR(SYSDATE,'HH:MI'), FIRST_NAME,LAST_NAME FROM V_USERS TO_CH FIRST_NAME LAST_NAME ----- ------------------------------ ------------------------------ 05:29 BRIAN ALDISS 05:29 POUL ANDERSON 05:29 NEAL ASHER SQL>
We still have only three records. We wait a little bit more and run the query again (remember that the synchronization occurs every 10 minutes).
SQL> SELECT TO_CHAR(SYSDATE,'HH:MI'), FIRST_NAME,LAST_NAME FROM V_USERS TO_CH FIRST_NAME LAST_NAME ----- ------------------------------ ------------------------------ 05:39 BRIAN ALDISS 05:39 POUL ANDERSON 05:39 NEAL ASHER 05:39 ROBERT ASPRIN SQL>
We can confirm that the fourth record appeared. Now the view is synchronized with the USERS table.
Additional considerations
The actual refreshing of the materialized view is started by calling the REFRESH procedure from the DBMS_REFRESH package. Using REFRESH NEXT SYSDATE + INTERVAL '10' MINUTE leads to a creation of a new JOB, that runs every 10 minutes and makes a call to DBMS_REFRESH.REFRESH.
We can check on this job by querying USER_JOBS (as user APP2).
SQL> SELECT JOB, LOG_USER, TO_CHAR(LAST_DATE,'DD/MM/YY HH:MI') LAST, TO_CHAR(NEXT_DATE,'DD/MM/YY HH:MI') NEXT, FAILURES FROM USER_JOBS; JOB LOG_USER LAST NEXT FAILURES ---- --------- --------------- --------------- ------------ 21 APP2 24/03/09 05:36 24/03/09 05:46 0 1 rows selected SQL>
The LAST column shows the time of last synchronization. NEXT provides information on when the next synchronization is supposed to start. There is also an INTERVAL column in USER_JOBS. If you inspect it's value for job 21 it will show SYSDATE+INTERVAL '10' MINUTE.
It is possible for a synchronization to fail (for example when the remote database is down). The number of failed refresh attempts is available in the FAILURES column.
If a job fails multiple times in a row (the exact number of required failures is determined by the max_failures parameter) it will enter a broken state. When this happens the system will stop trying to execute it according it's schedule. To fix this issue we have to manually remove the broken flag from the job. The easiest way to accomplish this is to call the DBMS_JOB.BROKEN procedure.
If we have more than a few jobs in the database, checking their status can become a tedious task. It is actually possible to automatically query USER_JOBS on a regular interval and try to fix any jobs that have entered broken state. To do this we can use a small procedure developed by Steven Feuerstein (from his book Oracle built-in packages) that looks like this:
Oracle built-in packages
от Steven Feuerstein
/* Filename on companion disk: job5.sql */*
CREATE OR REPLACE PROCEDURE job_fixer
AS
/*
|| calls DBMS_JOB.BROKEN to try and set
|| any broken jobs to unbroken
*/
/* cursor selects user's broken jobs */
CURSOR broken_jobs_cur
IS
SELECT job
FROM user_jobs
WHERE broken = 'Y';
BEGIN
FOR job_rec IN broken_jobs_cur
LOOP
DBMS_JOB.BROKEN(job_rec.job,FALSE);
END LOOP;
END job_fixer;
We can schedule it's execution by using DBMS_SCHEDULER (more information about it is available in the official documentation).