物化视图试验一则

创建时间： 2015-1-12 10:34 修改时间： 2015-1-12 13:12

试验环境：

db1 建立物化视图，同步db1上t1的资料

db2 源表t1

[oracle@test01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 21 18:31:25 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> set sqlprompt "_USER@ _CONNECT_IDENTIFIER as db2>"
SYS@ orcl as db2>create user user_mv identified by user_mv;

User created.

SYS@ orcl as db2>grant connect,resource to user_mv;

Grant succeeded.

SYS@ orcl as db2>conn user_mv/user_mv;
Connected.
USER_MV@ orcl as db2>create table t1(a integer);

Table created.

USER_MV@ orcl as db2>alter table t1 add(constraint t1_pk primary key(a));

Table altered.

-- grant materialized view 给user_mv

SQL> conn / as sysdba

Connected.

SQL> grant create materialized view to user_mv;

Grant succeeded.

USER_MV@ orcl as db2>create materialized view log on t1

2 with primary key

3 including new values;

Materialized view log created.

----增加 TNSname文件。

[oracle@rac01 admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/11.2.0/db_1/network/a
dmin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.37)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

SYSTEM@ orcl as db1>create user user_mv identified by user_mv;

User created.

SYSTEM@ orcl as db1>grant connect,resource to user_mv;

Grant succeeded.

SYS@ orcl as db1>grant create database link to user_mv;

Grant succeeded.

SYS@ orcl as db1>grant dba to user_mv;

Grant succeeded.

SYSTEM@ orcl as db1>conn user_mv/user_mv;
Connected.

SYS@ orcl as db1>create shared public database link db2
2 authenticated by user_mv
3 identified by user_mv
4 using 'orcl2';

Database link created.

USER_MV@ orcl as db1>select * from t1@db2;

no rows selected

--建立视图

USER_MV@ orcl as db1>create materialized view mv_t1
2 build immediate
3 refresh fast with primary key
4 on demand
5 enable query rewrite
6 as
7 select * from t1@db2;

Materialized view created.

USER_MV@ orcl as db1>select * from t1@db2;

A
----------
0
1

USER_MV@ orcl as db1>select * from mv_t1;

no rows selected

--在db2上面插入二笔资料

USER_MV@ orcl as db2>insert into t1 values(0);

1 row created.

USER_MV@ orcl as db2>insert into t1 values(1);

1 row created.

USER_MV@ orcl as db2>commit;

Commit complete.

--在db1上刷新，当然，手动是这们，也可作为job定时运行。

USER_MV@ orcl as db1>begin

2 dbms_mview.refresh('mv_t1','f');

3 end;

4 /

PL/SQL procedure successfully completed.

USER_MV@ orcl as db1>select * from mv_t1;

A

----------

0

1

出现了刚才插入的资料。。搞定。。

ITPub博客

物化视图试验一则

最新文章