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.

SQL> select * from t1;

ID SAL

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

1 60000

2 50002

3 50003

4 50004

5 50005

6 50006

8 50008

9 50009

10 50010

12 66666

10 rows selected.

七 目標端手動重新整理物化檢視，檢視同步是否成功

192.168.1.10

SQL> exec dbms_mview.refresh('MV_T1');

PL/SQL procedure successfully completed.

SQL> select * from mv_t1;

ID SAL

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

1 60000

2 50002

3 50003

4 50004

5 50005

6 50006

8 50008

9 50009

10 50010

12 66666

10 rows selected.

SQL> col segment_name for a8

SQL> select segment_name,segment_type from dba_segments where segment_name='MV_T1';

SEGMENT_ SEGMENT_TYPE

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

MV_T1 TABLE

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

Oracle 物化檢視例項一

Oracle 物化檢視 例項一

相關文章

Oracle 物化檢視例項一