Oracle物化视图创建全过程

一、准备条件以及备注

二、开始干活

1、首先要在目标端创建DB_LINK

2、在源端基表上创建Oracle物化视图快速刷新日志

3、目标端创建Oracle物化视图

4、视图刷新

5、测试物化视图的刷新

6、删除物化视图和物化视图日志

我们如果遇到需要从其它系统的数据库中取数据进行统计分析的问题，可疑选择使用ORACLE的ODI工具进行抽数，但是对方提供的数据库用户下没有任何对象，只是有查询所有表的权限，因此无法做数据反向。

于是决定使用物化视图，把对方数据库中的数据拿过来，虽然数据量比较大，但是每月只拿一次，而且如果设置成增量更新，也不会太慢。现在记录下物化视图的创建过程（以一张表为例）。

一、准备条件以及备注

假设双方数据库都是ORACLE11g ，需要同步过来的表名叫：TESTOBJ，对方数据库用户名：whview，密码：oracle，SID：DSGS

二、开始干活

1、首先要在目标端创建DB_LINK

在创建dblink前要先创建tnsname：todsgs

调用netmgr创建tnsname如下：

[oracle@dsgtarget admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TODSGS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.137)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DSGS)
)
)
然后创建dblink：
SQL> create database link to_dsgs connect to whview identified by oracle using 'TODSGS';
其中TODSGS为新建的到对方数据库的TNS。执行，现在我们就已经创建了到对方数据库的DB_LINK : to_dsgs

2、在源端基表上创建Oracle物化视图快速刷新日志
因为上面说过，以后视图的刷新将采用增量刷新的方式，因此，为配合增量刷新，ORACLE要求要在基表上建立物化视图日志。

在创建物化视图日志之前要保证基表上要有主键，因为物化视图是基于基表上的主键来实现物化视图和基表的同步刷新的。
基表的定义语句：
SQL> set long 999;
SQL> set pagesize 999;
SQL> set linesize 200;
SQL> select dbms_metadata.get_ddl('TABLE','TESTOBJ','WHVIEW') from dual;
DBMS_METADATA.GET_DDL('TABLE','TESTOBJ','WHVIEW')
--------------------------------------------------------------------------------
CREATE TABLE "WHVIEW"."TESTOBJ"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30),
CONSTRAINT "TODSGS_PK" PRIMARY KEY ("OBJECT_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_

对基表添加主键约束：
SQL> alter table whview.testobj add constraint testobj_pk primary key(object_id);

创建物化视图快速刷新日志
SQL> CREATE MATERIALIZED VIEW LOG ON TESTOBJ WITH PRIMARY KEY INCLUDING NEW VALUES;
物化视图日志创建完成后在源端会自动创建两张表：MLOG$_TESTOBJ、RUPD$_TESTOBJ
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MLOG$_TESTOBJ TABLE
RUPD$_TESTOBJ TABLE
TESTOBJ TABLE
MLOG$_TESTOBJ、RUPD$_TESTOBJ 是普通的表用来存放基表log数据的，占用oracle的磁盘空间 。所以在对源端做数据迁移同步时必须排除掉MLOG$_* 、RUPD$_*表。因为迁移到目标端以后这些基表上的log数据没有任何作用，只会浪费目标端的磁盘空间。
3、目标端创建Oracle物化视图
Oracle物化视图，从名字上面来开，它应该是属于视图，但是确实物化。其物化是针对普通视图并没有真正的物理存储而言，其实可以简单的把物化视图看做一个物理表（不再做具体解释）。
SQL> CREATE MATERIALIZED VIEW TESTOBJ --创建物化视图
2 BUILD IMMEDIATE --在视图编写好后创建
3 REFRESH FAST WITH PRIMARY KEY --根据主表主键增量刷新（FAST，增量）
4 ON DEMAND -- 在用户需要时，由用户刷新
5 ENABLE QUERY REWRITE --可读写
6 AS
7 SELECT * FROM TESTOBJ@to_dsgs; --查询语句

4、视图刷新
根据业务需要，每月不定时刷新，所以不能是JOB，而且数量多，所以也不能一个一个刷新。根据以上条件，选择使用ORACLE自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。该方法有两个参数，第一个参数是需要刷新的物化视图名称，第二个参数是刷新方式。我们可以写存储过来，对每个物化视图调用一次REFRESH方法，也可以使用“，”把物化视图连接以来，一次刷新。如下：
在目标端创建刷新物化视图的存储过程：
SQL> create or replace procedure P_MVIEW_REFRESH
2 as
3 begin
4 DBMS_MVIEW.REFRESH('TESTOBJ','f');
5 end P_MVIEW_REFRESH;
6 /
Procedure created.
或者使用：
SQL> create or replace procedure P_MVIEW_REFRESH
2 as
3 begin
4 DBMS_MVIEW.REFRESH('TESTOBJ,TESTOBJ2','ff');
5 end P_MVIEW_REFRESH;
6 /
Procedure created.
注意:
1、如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对

每个视图都要指明刷新方式（f、增量刷新，c、完全刷新，?、强制刷新）。

2、当日志和物化视图创建好后，删除日志，则需要重新创建物化视图，否则无法增量

刷新。

3、因为上面写的物化视图时根据主键进行更新，因此，主表必须有主键。

5、测试物化视图的刷新

在源端插入30条记录

SQL>insert into testobj
select * from dba_objects where rownum<=30 minus select * from testobj SQL>commit;

目标端查询物化视图仍然是原来的数据
SQL> select count(*) from TESTOBJ;
COUNT(*)
----------
10
执行刷新物化视图的存储过程
SQL> begin
2 P_MVIEW_REFRESH;
3 end;
4 /
PL/SQL procedure successfully completed.

再次查询物化视图结果显示是刷新后的与基表同步的物化视图
SQL> select count(*) from testobj;
COUNT(*)
----------
40

6、删除物化视图和物化视图日志
日志和物化视图要分开删除
DROP MATERIALIZED VIEW LOG ON TESTOBJ@to_dsgs;
DROP MATERIALIZED VIEW TESTOBJ;