Oracle创建物化视图

Oracle创建物化视图

由于要和远程端的数据库表格进行融合，假若不在一个库中，操作会非常繁琐，由此，我们拟采用物化视图来解决这个问题，下面我们来介绍一下他的基本步骤：

两台机器，一台机器的IP为：172.16.10.13，一台为172.16.10.15

我们先将15机器上的一个表格，远程物化到13上。

第一，创建测试用户：

15机器执行如下：

SQL> create user testuser1identified by hope;

用户已创建

SQL> grant connect,resource totestuser1;

授权成功。

13机器执行如下：

SQL> create user testuser2identified by hope;

用户已创建

SQL> grant dba totestuser2;

授权成功。

 

第二.在testuser1下创建一张表

SQL> conn testuser1/hope

已连接。

SQL> create tablestudent(

  2  pid intprimary key,

  3  namevarchar(20)

  4 );

表已创建。

第三，创建物化视图日志

SQL> create materialized view logon student;

实体化视图日志已创建。

第四，创建testuser2到testuser1的dblink

SQL> create database linkconn_testuser1 connect to testuser1 identified by hope

 2     using '(DESCRIPTION =

 3       (ADDRESS_LIST =

 4        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.15)(PORT = 1521))

 5       )

 6       (CONNECT_DATA =

 7         (SERVICE_NAME = tykm)

 8       )

 9      )'

 10 ;

数据库链接已创建。

【注意：】SERVICE_NAME可以通过：selectinstance_name from v$instance;查询

 

第五.在testuser2下创建物化视图

 

SQL> create materializedview mv_test refresh fast start with sysdate nextsysda

te+1/24*60  as select * fromstudent@conn_testuser1;

实体化视图已创建。

【这里：我们设置的是每分钟更新一次试图，但是貌似在我们Oracle服务器版本的机器上不支持】

第六，对创建完毕的物化视图进行commit处理。

第七，附加：若自动更新可用，此处可以不用执行以下步骤：

下面我们先做一个procedure,在procedure中，执行物化视图的手动刷新；然后，我们再做一个job，在job中定时的调用procedure就OK啦。

（1）创建procedure:

edhope:打开文件填入一下内容：

 

create orreplace procedure ptest

as

begin

dbms_mview.refresh(list => 'mv_test' ,method=> 'c');

end ;

关闭文件

执行get hope 

/

即可完成

(2)编写job

ed basil打开文本文档

 

declare

v_job number;

begin

dbms_job.submit(v_job, 'ptest;', sysdate, 'SYSDATE +1/1440');

end;

编写完成后，执行

get basil

/

即可完成

完成后，job就可以定时执行啦~

总结：当我们在15服务器的表格中插入数据记录是，1分钟后，会在13服务器表格中进行显示。

--------------------------------------------------------------------------------------

附带一个实际的例子：

我们连接对方的数据库：对方将log表创建好，告诉我方用户名、密码及sid信息。

我方操作如下：

 

第一，创建数据库连接

SQL> create database linkconn_yixun connect to newspaper identified by "newspap

er"     using '(DESCRIPTION =       (ADDRESS_LIST =       (ADDRESS = (PROTOCO

L =TCP)(HOST = 172.16.10.252)(PORT = 1521))       )       (CONNECT_DATA=

     (SERVICE_NAME = orcl)  )      )' ; 

数据库链接已创建。 

第二，创建物化视图v_yixun_click

SQL> create materialized view mv_yixun_clickrefresh fast start with sysdate nex

tsysdate+1/24*60  as select * fromt_use@conn_yixun; 

实体化视图已创建。

SQL> exec dbms_refresh.refresh('mv_yixun_click '); 

PL/SQL 过程已成功完成。

第三， 创建物化视图mv_yixun_user

SQL> create materialized view mv_yixun_userrefresh fast start with sysdate next

 sysdate+1/24*60 as select * fromt_wap_user@conn_yixun; 

实体化视图已创建。

 SQL> exec dbms_refresh.refresh('mv_yixun_user '); 

PL/SQL 过程已成功完成。

第四，创建存储过程，以便更新视图

create orreplace procedure proc_yixun

as

begin

dbms_mview.refresh(list => 'mv_yixun_click',method => 'c');

dbms_mview.refresh(list => 'mv_yixun_user',method => 'c');

//因为我们要更新两个表，所以，这里执行了两次更新，分别更新不同的表格

end;

 第五， 创建job以便定时执行存储过程

declare

v_jobnumber;

begin

dbms_job.submit(v_job, 'proc_yixun;', sysdate,'sysdate+1/(24*60)');

end;

第六，可以通过以下语句查看job的运行情况： 

selectjob, what, next_date, interval, failures from user_jobs;