Creating DB Links in Oracle

Syntax:

CREATE DATABASE LINK ABC.company.com
CONNECT TO schema/username
IDENTIFIED BY password
USING ‘ABC.company.com’;

1. ABC.company.com – name of DB link usually matches
2. schema/username – atleast one shema & username will be same
3. password
4. ‘ABC.company.com’ – Tnsping service name and find this one

Note: Don’t use single/double quotes for first 3 items.

Materialized View REFRESH FAST ON COMMIT (Real-time/Live Refresh) – Same Environment no DB Links used

Below steps gives an example of creating fast refresh materialized views. “Fast Refresh” are the keywords used to create Real-time/Live MVs.

DB Links used to establish channel/route between DB server, eventually you can query remote DB with simple query like select * from table@remote.db.com

Step 1:
CREATE TABLE MV_TestTable(ID NUMBER, NAME VARCHAR2(25), CONSTRAINT MV_TestTable_pk PRIMARY KEY(ID));
Step 2:
CREATE MATERIALIZED VIEW LOG ON MV_TestTable WITH ROWID, PRIMARY KEY;
Step 3:
insert into MV_TestTable values (1,’one’);
insert into MV_TestTable values (2,’two’);
insert into MV_TestTable values (3,’three’);
insert into MV_TestTable values (4,’four’);
Step 4:
CREATE MATERIALIZED VIEW MVLIVE_TestTable
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS SELECT MV_TestTable.*,
MV_TestTable.ROWID AS Test_Table_rowid
FROM MV_TestTable;
Step 5:
Select * from MVLIVE_TestTable;
Returns 4 records
Step 6:
insert into MV_TestTable values (5,’three’);
insert into MV_TestTable values (6,’four’);
commit;
Step 7:
Select * from MVLIVE_TestTable;
Returns 6 records

Point to Ponder: All front-end applications and loader applications will commit the table after loading the data so this approach works very solid.