OCM实验-物化视图

最新推荐文章于 2024-09-13 15:23:34 发布

转载最新推荐文章于 2024-09-13 15:23:34 发布 · 184 阅读

本文介绍如何在Oracle数据库中创建跨库物化视图，并实现手动刷新及尝试自动快速刷新的方法。通过实例演示了不同场景下物化视图的行为，包括不同数据库间的数据同步限制。

要求创建跨库物化视图并可手动更新

1）创建基表。在catalog所在的数据库，新建用户并创建一个基表t,插入一些测试记录

SYS@OCM2>select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS
SYSAUX
TEMPTS1
USERS
MGMT_TABLESPACE
MGMT_ECM_DEPOT_TS
CATALOG_TBS

8 rows selected.

SYS@OCM2>create user mv_admin identified by mv_admin default tablespace users;

User created.

SYS@OCM2>grant connect,resource to mv_admin;

Grant succeeded.

SYS@OCM2>conn mv_admin/mv_admin
Connected.
MV_ADMIN@OCM2>create table t (x varchar2(20));

Table created.

MV_ADMIN@OCM2>insert into t values ('tns');

1 row created.

MV_ADMIN@OCM2>commit;

Commit complete.

MV_ADMIN@OCM2>select * from t;

X
--------------------
tns

2）创建物化视图日志

MV_ADMIN@OCM2>create materialized view log on t with rowid;

Materialized view log created.

3）在PROD库，也就是物化视图所在的库，创建dblink

SYS@PROD>show parameter global;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
global_names                         boolean     FALSE
SYS@PROD>
SYS@PROD>
SYS@PROD>create public database link db_link_ocm2 connect to mv_admin identified by mv_admin using 'ocm2';

Database link created.

4）在PROD库，创建一个用户并创建物化视图

SYS@PROD>create user mv_user identified by mv_user;

User created.

grant">SYS@PROD>grant dba to mv_user;

Grant succeeded.

SYS@PROD>create materialized view mv_t refresh fast with rowid as select * from t@db_link_ocm2;

Materialized view created.

SYS@PROD>select * from mv_t;

X
--------------------
tns

5）在基表插入数据，检查物化视图，是否自动同步还是需要手动同步

MV_ADMIN@OCM2>insert into t values ('new values');

1 row created.

MV_ADMIN@OCM2>commit;

Commit complete.

MV_ADMIN@OCM2>select * from t;

X
--------------------
tns
new values

检查物化视图

SYS@PROD>select * from mv_t;

X
--------------------
tns

数据没有自动同步

快速刷新方法：增量刷新或完全刷新方法：全表刷新
exec dbms_mview.refresh('mv_t','f'); 手动刷新物化视图
exec dbms_mview.refresh('mv_t','c');

SYS@PROD>exec dbms_mview.refresh('mv_t','f');

PL/SQL procedure successfully completed.

SYS@PROD>select * from mv_t;

X
--------------------
tns
new values

手动同步完成。

要求创建物化视图具有自动快速刷新功能，说明，这个实时只能在基表与物化视图在同一个数据库中。

以下实验是基于不同的数据库

1）新建基表t1

MV_ADMIN@OCM2>create table t1 (a varchar2(20),b varchar2(20),sal number(18,2));

Table created.

MV_ADMIN@OCM2>insert into t1 values ('x','y',23.5);

1 row created.

MV_ADMIN@OCM2>commit;

Commit complete.

2）创建物化视图日志

MV_ADMIN@OCM2>create materialized view log on t1 with rowid;

Materialized view log created.

MV_ADMIN@OCM2>drop materialized view log on t1;

Materialized view log dropped.

MV_ADMIN@OCM2>create materialized view log on t1 with sequence,rowid(a,b,sal) including new values;

Materialized view log created.

3）在PROD库，新建物化视图

MV_USER@PROD>create materialized view mv_t1 build immediate refresh with rowid fast on commit enable query rewrite as select a,b from t1@db_link_ocm2;
create materialized view mv_t1 build immediate refresh with rowid fast on commit enable query rewrite as select a,b from t1@db_link_ocm2
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

MV_USER@PROD>create materialized view mv_t1 build immediate refresh with rowid enable query rewrite as select a,b from t1@db_link_ocm2;

Materialized view created.

测试

MV_ADMIN@OCM2>insert into t1 values('q','w',21.4);

1 row created.

MV_ADMIN@OCM2>commit;

Commit complete.

物化视图

MV_USER@PROD>select * from mv_t1;

A B
-------------------- --------------------
x y

物化视图没有实时同步

MV_USER@PROD>create materialized view mv_t1 build immediate refresh with rowid fast on demand enable query rewrite as select rowid r,a,b from t1@db_link_ocm2;

Materialized view created.

MV_ADMIN@OCM2>insert into t1 values('e','r',20);

1 row created.

MV_ADMIN@OCM2>commit;

Commit complete.

MV_USER@PROD>select * from mv_t1;

R                  A                    B
------------------ -------------------- --------------------
AAAELAAAEAAAAASAAA x                    y
AAAELAAAEAAAAASAAB q                    w

MV_USER@PROD>exec dbms_mview.refresh('mv_t1','f');

PL/SQL procedure successfully completed.

MV_USER@PROD>select * from mv_t1;

R                  A                    B
------------------ -------------------- --------------------
AAAELAAAEAAAAASAAA x                    y
AAAELAAAEAAAAASAAB q                    w
AAAELAAAEAAAAASAAC e                    r

物化视图没有实时同步。

原因说明：

This materialized view is selecting from a remote table over a database link (a distributed materialized view). For "on commit", you can use only if you have your master table in the same database where you are creating the materialized view. Therefore, on commit is not supported in remote databases. Another way you can use remote database if you replace your line "REFRESH ON COMMIT" with "refresh fast on demand with primary key" and this works.

The docs say this about the ORA-21054 error:

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Cause: The materialized view did not satisfy conditions for refresh at commit time.

Action: Specify only valid options.

There are restrictions on using the ON COMMIT refresh option in materialized views. For complete explanations of the ORA-12054 issue, see the book “Oracle Replication”.

There are restrictions on using the ON COMMIT refresh option in materialized views.

Include the primary key in the select clause of the materialized view
Make sure to create the materialized view log on the master table
To create a materialized view with the on commit refresh clause) you must have the on commit refresh object privilege on any master tables that you do not own or you must have the on commit refresh system privilege

参考文档：

http://www.dba-oracle.com/t_ora_12054_cannot_set_on_commit_refresh_materialized_view.htm

以下实验是基于同一个数据库：

1）创建基表并添加测试数据

MV_USER@PROD>create table t (a varchar2(20),s varchar2(20),d varchar2(20));

Table created.

MV_USER@PROD>insert into t values ('q','w','e');

1 row created.

MV_USER@PROD>commit;

Commit complete.

2）创建物化视图日志

MV_USER@PROD>create materialized view log on t with sequence,rowid (a,s,d) including new values;