Oracle 物化视图实例一

最新推荐文章于 2021-04-09 21:35:42 发布

ckth47350

最新推荐文章于 2021-04-09 21:35:42 发布

阅读量224

点赞数

文章标签：数据库网络开发工具

Oracle 物化视图的创建与使用

一 数据库版本

目标端

192.168.1.10

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

源端

192.168.1.20

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

二 目标端创建连接源端的网络连接

192.168.1.10

[oracle11@localhost ~]$ cd /u2/app/oracle/product/11.2.0/db_1/network/admin/

[oracle11@localhost admin]$ vim tnsnames.ora

20 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = zxaaa)

)

[oracle11@localhost admin]$ tnsping 20

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 13-MAY-2015 10:07:22

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zxaaa)))

OK (0 msec)

三 源端创建测试表

192.168.1.20

SQL> conn chen/chen

Connected.

SQL> create table t1(id number,sal number);

Table created.

SQL> alter table t1 add constraint pk_t1 primary key(id);

Table altered.

SQL> declare

2 begin

3 for i in 1..10 loop

4 insert into t1 values(i,50000+i);

5 commit;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

SQL> select * from t1;

ID SAL

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

1 50001

2 50002

3 50003

4 50004

5 50005

6 50006

7 50007

8 50008

9 50009

10 50010

10 rows selected.

四 目标端创建连接源端测试表的DBLINK

192.168.1.10

SQL> create database link zxp2p connect to chen identified by chen using '20';

Database link created.

SQL> select * from t1@zxaaa;

ID SAL

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

1 50001

2 50002

3 50003

4 50004

5 50005

6 50006

7 50007

8 50008

9 50009

10 50010

10 rows selected.

五 目标端创建物化视图

192.168.1.10

SQL> create materialized view mv_t1 as select * from t1@zxaaa;

Materialized view created.

默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。

物化视图的数据怎么随着基表而更新？

Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。

六 源端更改测试表数据

192.168.1.20

SQL> update t1 set sal=60000 where id=1;

1 row updated.

SQL> delete t1 where id=7;

1 row deleted.

SQL> insert into t1 values(12,6666);

1 row created.

SQL> update t1 set sal=66666 where id=12;

1 row updated.

SQL> commit;

Commit complete.