Feeds:
Posts
Comments

Posts Tagged ‘view’

A view in oracle is nothing but a stored sql scripts. View itself contain no data. Whenever we query on a view underlying SQL scripts is executed. Suppose I have created a view as CREATE VIEW TEST_VIEW AS SELECT NAME FROM TEST; Now whenever I query on view TEST_VIEW underlying script SELECT NAME FROM TEST is executed.

A view can be queried, and the data it represents can be changed. Data in a view can be updated or deleted, and new data inserted. These operations directly alter the tables on which the view is based and are subject to the integrity constraints and triggers of the base tables.

Workaround Example:
Connect as a dba user and grant only insert privilege to table TEST on arju schema table to india.

SQL> CONN ARJU/A
Connected.

SQL> revoke SELECT ON TEST from india;
Revoke succeeded.

SQL> grant insert ON TEST TO INDIA;
Grant succeeded.

SQL> CONN INDIA/T
Connected.
SQL> CREATE VIEW ARJU_SCHMEA_VIEW AS SELECT * FROM ARJU.TEST;
View created.

Since india has only insert privilege on the base table so he can create view but can’t select any row from the base table or view.

SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
SELECT * FROM ARJU_SCHMEA_VIEW
*
ERROR at line 1:
ORA-01031: insufficient privileges
Now grant him select privilege on the base table.

SQL> CONN ARJU/A
Connected.
SQL> grant SELECT ON TEST TO INDIA;
Grant succeeded.

SQL> CONN INDIA/T
Connected.
SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
no rows selected

Now insert row in to the view.
SQL> INSERT INTO ARJU_SCHMEA_VIEW VALUES(1);
1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
A
———-
1

SQL> SELECT * FROM ARJU.TEST;
A
———-
1

So if underlying view is modified base table is affected.

Related post:

https://samadhandba.wordpress.com/2010/12/29/materialized-views-in-oracle/

https://samadhandba.wordpress.com/2011/02/04/benefits-of-using-views/

Read Full Post »

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.

In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.

Primary Key Materialized Views
The following statement creates the primary-key materialized view on the table emp located on a remote database.

SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;

Materialized view created.

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.

Rowid Materialized Views
The following statement creates the rowid materialized view on table emp located on a remote database:

SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote_db;

Materialized view log created.

Subquery Materialized Views

The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:

SQL> CREATE MATERIALIZED VIEW mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
(SELECT * FROM dept@remote_db d
WHERE e.dept_no = d.dept_no)

REFRESH CLAUSE
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]

The refresh option specifies:

The refresh method used by Oracle to refresh data in materialized view
Whether the view is primary key based or row-id based
The time and interval at which the view is to be refreshed
Refresh Method – FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.

You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.

SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.

Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method – COMPLETE Clause
The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method – FORCE Clause
When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause
WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

Rowid materialized views should have a single master table and cannot contain any of the following:

Distinct or aggregate functions
GROUP BY Subqueries , Joins & Set operations
Timing the refresh
The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes

SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 2
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;

Materialized view created.

In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Related post:

https://samadhandba.wordpress.com/2010/12/29/materialized-views-in-oracle/

https://samadhandba.wordpress.com/2011/02/04/what-is-view-in-oracle/

https://samadhandba.wordpress.com/2011/02/04/benefits-of-using-views/

Read Full Post »