BASIC REPLICATION MASTER – VIEW (Materialized View)

9 Sep

########################################
# Materialized View
# Setup di sisi MASTER
########################################
1. Materialized View Log at Master Site
[oracle@iscdd ~]$ sqlplus scott/tiger@iscdd
SQL> CREATE TABLE dept_ku AS SELECT * FROM dept;
SQL> CREATE MATERIALIZED VIEW LOG ON dept_ku;
CREATE MATERIALIZED VIEW LOG ON dept_ku
*
ERROR at line 1:
ORA-12014: table ‘DEPT_KU’ does not contain a primary key constraint

— Materialized view log tidak dapat dibuat pada tables tanpa primary key.

SQL> desc dept_ku;
Name                                      Null?    Type
—————————————– ——– —————————-
DEPTNO                                             NUMBER(2)
DNAME                                              VARCHAR2(14)
LOC                                                VARCHAR2(13)

SQL> ALTER TABLE dept_ku ADD CONSTRAINT pk_dept_ku PRIMARY KEY (deptno);
SQL> CREATE MATERIALIZED VIEW LOG ON dept_ku;

########################################
# Setup di sisi VIEW SITE
########################################
2. Set up Materialized View Site
[oracle@iscdd2 ~]$ sqlplus system@iscdd2
SQL> CREATE USER remote_user IDENTIFIED BY abc DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
SQL> GRANT connect, resource, create materialized view, create database link TO remote_user;

3. Database Link
SQL> connect remote_user/abc@iscdd2
SQL> CREATE DATABASE LINK iscdd_ku CONNECT TO scott IDENTIFIED BY tiger USING ‘iscdd’;

4. Materialized View
SQL> CREATE MATERIALIZED VIEW mv1_ku REFRESH FAST AS SELECT * FROM scott.dept_ku@iscdd_ku;

5. Refresh Group
SQL> BEGIN
dbms_refresh.make(
name => ‘mv1_refgroup’,
list => ‘mv1_ku’,
next_date => sysdate,
—- 5 menit refresh
interval => ‘sysdate + 1 / ((24*60)/5)’,
implicit_destroy => true,
lax => true);
END;
/

6. Refresh Manual
SQL> exec dbms_refresh.refresh(name => ‘mv1_refgroup’);

########################################
# TEST — TEST
########################################
########################################
# Master Site
########################################
7. test update di master site, hasil lihat di view site
[oracle@iscdd dbs]$ sqlplus scott/tiger@iscdd
SQL> select * from dept_ku;
DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
SQL> insert into dept_ku values(50,’IT’,’Lamongan’);
SQL> commit;
SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:42:09
SQL> select to_char(sysdate + 1/((24*60)/5), ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:47:09

########################################
# View Site
########################################
[oracle@iscdd2 ~]$ sqlplus remote_user/abc@iscdd2
SQL> select * from mv1_ku;
DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:42:59
SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:50:32
SQL>  select * from mv1_ku;

DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
50 IT             Lamongan

2 Responses to “BASIC REPLICATION MASTER – VIEW (Materialized View)”

  1. komsi January 13, 2009 at 4:26 am #

    Alhmdulillah sudah saya coba dan berhasil, Basic replication berarti object yang ada di server remote harus view ya?
    Apakah memungkinkan di basic replication mereplikasi semua table dalam suatu user, sebagai contoh jika kita create table di server master agar bisa otomatis membuat replikasi di server remote.

    Trima kasih

    • setijoagus January 14, 2009 at 1:07 am #

      Puji Tuhan, wah aku juga senang Kl sampeyan sudah berhasil
      contoh saya Basic replication hanya untuk view doang
      setahu saya replication “hanya” mereplikasi “isi” sebuah table, untuk create table di server master agar bisa otomatis membuat replikasi di server remote, otomatis juga nggak bisa

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: