Table replication using Materialized View in oracle 11g

Source Database:prim
Target Database:-stand


Step 1.create the USERS table and add some sample records inside.

At source database:-
[oracle@server1 ~]$ sqlplus sys/system as sysdba@prim
SQL> create user appuser identified by appuser;
SQL> GRANT CONNECT,RESOURCE TO appuser;
SQL> conn appuser/appuser
Connected.

SQL> insert into users values (1 ,'RAM','DAS','REDHAT');
SQL> insert into users values (2, 'SHYAM','SINGH','SUSE');
SQL> insert into users values (3, 'ROHAN','SHARMA','UBUNTU');
SQL> COMMIT;
Commit complete.

At target database:-
Step 2.Now before creating database link we need to add entry inside tnsnames.ora file
so that the target database can connect to the source database.

[oracle@server1 ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
STAND =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server2.soumya.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stand)
    )
  )

prim =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.soumya.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRIM)
    )
  )
 
 :wq
 so we added the tnsentry for source database i.e. prim inside tnsnames.ora file

Step 3.Now connect to the local database i.e. target and create a user appuser2

[oracle@server2 ~]$ sqlplus sys/system as sysdba@stand
SQL> create user appuser2 identified by appuser2;
SQL> grant connect , resource , create database link , create materialized view to appuser2;
SQL> conn appuser2/appuser2
Connected.

At target database:-
Step 3.Now create a database link to connect source database from appuser2
[oracle@server2 ~]$ sqlplus appuser2/appuser2@stand
SQL> CREATE DATABASE LINK stand CONNECT TO appuser IDENTIFIED BY appuser USING 'prim';
CREATE DATABASE LINK stand CONNECT TO appuser IDENTIFIED BY appuser USING 'prim'
                           *
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

While creating the database link i was facing the above error and i fixed it using the below steps
SQL>select * from global_name;
STAND
-- write down the current name;

SQL> Alter database rename global_name to tttt.xxx;

SQL> CREATE DATABASE LINK mylink CONNECT TO appuser IDENTIFIED BY appuser USING 'prim';

Rename global name back to original from the query above.

SQL> Alter database rename global_name to STAND.soumya.com;

Now You must be able to Select any table from user APP1 On Source Database from target database.
At target database:-
[oracle@server2 ~]$ sqlplus appuser/appuser@prim
SQL> select * from users;

   USER_ID FIRST_NAME LAST_NAME                      PASSWORD
---------- ---------- ------------------------------ --------------------
         1 RAM        DAS                            REDHAT
         2 SHYAM      SINGH                          SUSE
         3 ROHAN      SHARMA                         UBUNTU


Step 4.Now we will create materialized view On target Database:-
SQL> CREATE MATERIALIZED VIEW V_USERS REFRESH NEXT
  SYSDATE+INTERVAL '10' MINUTE WITH ROWID AS SELECT * FROM users@mylink;

Step 5. Now test the scenario:-
[oracle@server2 ~]$ sqlplus appuser2/appuser2@stand
At target database:-
SQL> select count(*) from V_USERS;
It Must Give you same Number Of Row In appuser@prim , If you want to test synchronization
All you have to do is

At source database:-
SQL> conn appuser/appuser
SQL> INSERT INTO USERS VALUES (4, 'ROBERT', 'ASPRIN', 'PASS4')

And then check at target database after 10mins.It would be in sync.