# Configure and manage distributed materialized views

**URL:** <https://www.batmunkh.net/t/configure-and-manage-distributed-materialized-views/157>\
**Category:** Tutorials, Solutions\
**Tags:** distributed, materialized, view, views\
**Created:** [February 15, 2017, 11:42pm UTC](https://www.batmunkh.net/t/configure-and-manage-distributed-materialized-views/157 "2017-02-15T23:42:13Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![batmunkh](https://www.batmunkh.net/user_avatar/www.batmunkh.net/batmunkh/32/297_2.png) [@batmunkh](https://www.batmunkh.net/u/batmunkh)\
**Post date:** [February 15, 2017, 11:42pm UTC](https://www.batmunkh.net/t/configure-and-manage-distributed-materialized-views/157/1 "2017-02-15T23:42:13Z")

</div>

Distributed гэдэг нь өөр серверт буй мэдээллээс materialized view үүсгэн мэдээлэл татах тухай юм.

Дараах жишээнд ашиглагдах зүйлс:

TEST1 баазын SCOTT schema – MVIEW-ийг энд үүсгэнэ. ([node215.oracle.me](http://node215.oracle.me))  
EMTEST баазын HR schema – эндээс мэдээллийг татна. ([node214.oracle.me](http://node214.oracle.me))

HR schema руу холбогдох боломжийг бүрдүүлнэ. ([node215.oracle.me](http://node215.oracle.me))

```
[oracle@node215 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
EMTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node214.oracle.me)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = emtest)
    )
  )

```

HR хэсэгт mview log иэдэвхжүүлнэ. ([node214.oracle.me](http://node214.oracle.me))

```
CREATE MATERIALIZED VIEW LOG ON EMPLOYEES;

```

SCOTT хэрэглэгч HR руу холбогдох холбоос үүсгэнэ. ([node215.oracle.me](http://node215.oracle.me))

```
CREATE PUBLIC DATABASE LINK EMTEST CONNECT TO HR IDENTIFIED BY "hr" USING 'EMTEST';

```

SCOTT хэрэглэгчээр холболтыг шалгаж MVIEW үүсгэж мэдээллийг татах.

```
SELECT COUNT(*) FROM EMPLOYEES@EMTEST;

CREATE MATERIALIZED VIEW EMP_MV REFRESH FAST AS
  SELECT * FROM EMPLOYEES@EMTEST;

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

```

Шалгах

```
SQL>
SQL> desc emp_mv;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID NOT NULL NUMBER(6)
 FIRST_NAME VARCHAR2(20)
 LAST_NAME NOT NULL VARCHAR2(25)
 EMAIL NOT NULL VARCHAR2(25)
 PHONE_NUMBER VARCHAR2(20)
 HIRE_DATE NOT NULL DATE
 JOB_ID NOT NULL VARCHAR2(10)
 SALARY NUMBER(8,2)
 COMMISSION_PCT NUMBER(2,2)
 MANAGER_ID NUMBER(6)
 DEPARTMENT_ID NUMBER(4)

SQL> desc employees@EMTEST;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID NOT NULL NUMBER(6)
 FIRST_NAME VARCHAR2(20)
 LAST_NAME NOT NULL VARCHAR2(25)
 EMAIL NOT NULL VARCHAR2(25)
 PHONE_NUMBER VARCHAR2(20)
 HIRE_DATE NOT NULL DATE
 JOB_ID NOT NULL VARCHAR2(10)
 SALARY NUMBER(8,2)
 COMMISSION_PCT NUMBER(2,2)
 MANAGER_ID NUMBER(6)
 DEPARTMENT_ID NUMBER(4)

SQL>

```

## Materialized View Refresh Group

* * *

```
-- HR
CREATE MATERIALIZED VIEW LOG ON DEPARTMENTS;

```

MVIEW үүсгэх

```
-- SCOTT
CREATE MATERIALIZED VIEW DEP_MV REFRESH FAST AS
SELECT * FROM DEPARTMENTS@EMTEST;

```

REFRESH GROUP үүсгэх

```
-- SCOTT
BEGIN
DBMS_REFRESH.MAKE (
  NAME => 'REFRESH_GROUP_TEST',
  LIST => 'EMP_MV,DEP_MV',
  NEXT_DATE => SYSDATE,
  INTERVAL => 'SYSDATE+1/1440',
  IMPLICIT_DESTROY => TRUE);
END;
/

```

Турших зорилгоос үүсгэсэн мэдээллүүдийг цэвэрлэх

```
-- SCOTT
EXEC DBMS_REFRESH.DESTROY('REFRESH_GROUP_TEST');
DROP MATERIALIZED VIEW EMP_MV;
DROP MATERIALIZED VIEW DEP_MV;
DROP PUBLIC DATABASE LINK EMTEST;

-- HR
DROP MATERIALIZED VIEW LOG ON EMPLOYEES;
DROP MATERIALIZED VIEW LOG ON DEPARTMENTS;
```