Configure and manage distributed materialized views

Tutorials, Solutions
, , ,
1

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

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

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

HR schema руу холбогдох боломжийг бүрдүүлнэ. (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)

CREATE MATERIALIZED VIEW LOG ON EMPLOYEES;

SCOTT хэрэглэгч HR руу холбогдох холбоос үүсгэнэ. (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;