DB Links and Mview

Database Links:

Database links are useful to establish a link between two Database users over the network.

These database links also can be used between two users with in a single Database instance.

Minimum requirements to create the Database links:-

To create the Database links the user should get create database link privilege (permission) from dba. To use the database link listener service must be started at server side and tnsnames.ora file must be configured at client side.

Demo’s :-

create dblink between prod database and development database and acess kishore schema.

$pwd

Goto prod database:-

$lsnrctl status <listener name>

If not started, start using below

$lsntctl start <listener name>

Make sure database is running.

sys>select name,open_mode from v$database;

sys>conn kishore/kishore

kishore@PROD>select * from tab;

kishore@PROD>select * from demo;

goto development database :- (dbalearning1)

$sqlplus / as sysdba

sys@ development >select * from all_users;

sys@ development >grant create database link to u1;

sys@ development >exit

$cd $ORACLE_HOME/network/admin

Check tns status

$tnsping hcp

$sqlplus u1/1u1

U1@ development >select * from tab;

Syntax:

Create database_link linkname connect to username identified by Password using ‘Service Name’;

Example:

U1@ development >create database link hacs connect to kishore identified by kishore using ’hcp’;

U1@ development >select * from user_db_links;

Acess data using DB Link

Select * from object_name@dblinkname;

Example:

U1@ development >select * from demo@hacs;

U1@ development >insert into demo@hacs values(9999);

U1@ development >commit;

got production database :

kishore@PROD>select * from demo;   [Check new inserted records.]

MATERIALIZED VIEW

 

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views.

Snapshots are called as materialized views from oracle 9inwards

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

Materialized views are defined or created by a query, are stored in a physical object, and follow certain refresh mechanisms. This refresh method keeps the materialized view up to date.

When creating a materialized view, you have the option of specifying whether the refresh occurs ON DEMAND or ON COMMIT or BY TIME. In case of an ON COMMIT scenario, when a transaction commits, the materialized view is refreshed and as a result, the data is always current in a materialized view. In case of an ON DEMAND type, calling the dbms_mview package procedures refreshes the materialized view.In case of BY TIME materialized view gets update periodically based on the time limit

Materialized Views Refresh Types

Refresh is the operation that synchronizes the content of the materialized view with the data in base tables. Following are the types of refresh:

Complete: Involves truncating existing data & re-inserting all the data based on the detail tables by re-executing the query definition from the create command.

Fast: Apply the changes made since the last refresh.

a)Fast refresh using materialized view logs

b)Fast refresh using ROWID range: Can do the fast refresh after the direct load, based on the rowed of the new rows.

Force: First tries to refresh with fast mechanism if possible or else will use a complete refresh. This is default refresh type.

Creation of materialized views :-

sys>show parameter global_names=true

Note :- In the distributed Database environment (or) client and server architecture 

global_names parameter must be true at server database.

and global_names parameter must be false in the client machine (or) client database.

Goto development database:

sys@dev>grant create materialized view to u1;

sys@dev>conn u1/u1;

COMPLETE REFRESH TYPE and BY TIME OPTION

ali@dev> create materialized view matdemo

                 refresh complete

                 start with sysdate next sysdate + 1/(24*60*60)

                 as select * from demo@hacs;

u1@dev>select * from tab;

u1@dev>select * from matdemo;

COMPLETE REFRESH TYPE and ON COMMIT OPTION

U1@dev> CREATE MATERIALIZED VIEW mv_simple
TABLESPACE uwdata
BUILD IMMEDIATE
REFRESH completed ON COMMIT AS
SELECT * FROM servers;

u1@dev>select * from tab;

u1@dev>select * from mv_simple;

FAST REFRESH TYPE and ON DEMAND OPTION

For FAST refresh we need mvlog.So create it first at serverside

sys>CREATE MATERIALIZED VIEW LOG ON scott.emp

TABLESPACE users

WITH PRIMARY KEY

INCLUDING NEW VALUES;

Now create mview at client side

 U1@dev> CREATE MATERIALIZED VIEW mv_emp

REFRESH FAST ON DEMAND

AS

Select * from emp@hacs;

As it was ON DEMAND OPTION we have to refreash manually using the below.

 EXEC DBMS_MVIEW.REFRESH('mv_emp', 'F');

u1@dev>select * from tab;

u1@dev>select * from mv_emp;

goto production server

sys>conn kishore/kishore

kishore@PROD>insert into demo values(5555);

kishore@PROD>commit;

goto development server

u1@dev>select * from matdemo;

How to drop a materialized view :-

Goto client

U1@dev>drop materialized view matdemo;

How to know when was the last refresh happened on materialized views:

SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;

Related Views

DBA_MVIEWS

DBA_MVIEW_LOGS

DBA_MVIEW_KEYS

DBA_REGISTERED_MVIEWS

DBA_REGISTERED_MVIEW_GROUPS

DBA_MVIEW_REFRESH_TIMES