当前位置：首页 > article >正文

利用物化视图刷新同步表记录(2-rowid)

article 2025/2/22 2:03:15

利用物化视图同步r1表记录到目标库的mv_r2上，续上篇

利用物化视图刷新同步表记录-CSDN博客

简要步骤：

1.源表

create table r1(id int,name varchar2(30));

2.创建源表日志--可选，不建则不能增量刷新exec dbms_mview.refresh('r3',method => 'f');

create materialized view log on r1 with rowid;可以是主键，如果有

create materialized view log on r1 with primary key;

3.创建dblink

create database link r2_r1 connect to jyc identified by jyc using '192.168.205.57:1521/lndb';

4.创建物化视图，名字可以和源表相同或不同

create materialized view mv_r2 refresh force on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1;

================================================

以下测试记录：

SQL> create table r1(id int,name varchar2(30));

Table created.

SQL> create materialized view log on r1 with rowid;

Materialized view log created.

SQL> create table r2 as select * from r1 where 1=2;

Table created.

SQL> create database link r2_r1 connect to jyc identified by jyc using '192.168.205.57:1521/lndb';

Database link created.

SQL> select * from dual@r2_r1;

D
-
X

SQL> create materialized view mv_r2 refresh force on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1;

Materialized view created.

SQL> drop materialized view mv_r2;

Materialized view dropped.

SQL> drop materialized view log on r1;

Materialized view log dropped.

SQL> create materialized view mv_r2 refresh force on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1;

Materialized view created.

SQL> insert into r1 values(1,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from r2;

no rows selected

SQL> set time on
09:03:21 SQL> /

no rows selected

09:03:25 SQL> select * from r1;

ID NAME
---------- ------------------------------
1 a

09:03:31 SQL> select * from t2;

ID NAME
---------- ------------------------------
2 b
1 a
3 a

09:03:36 SQL> select * from r2;

no rows selected

09:03:40 SQL> select * from r1@r2_r1;

ID NAME
---------- ------------------------------
1 a

09:03:59 SQL> select * from r2;

no rows selected

09:04:05 SQL> /

no rows selected

09:04:13 SQL> /

no rows selected

09:04:19 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a

09:04:26 SQL> select * from r2;

no rows selected

09:04:32 SQL> /

no rows selected

09:05:25 SQL> exec dbms_mview.refresh('R2',method => 'c');
BEGIN dbms_mview.refresh('R2',method => 'c'); END;

*
ERROR at line 1:
ORA-23401: materialized view "JYC"."R2" does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1

09:06:20 SQL> exec dbms_mview.refresh('r2',method => 'c');
BEGIN dbms_mview.refresh('r2',method => 'c'); END;

09:06:29 SQL> exec dbms_mview.refresh('mv_r2',method => 'c');

PL/SQL procedure successfully completed.

09:06:46 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a

09:06:51 SQL> select * from r2;

no rows selected

09:07:23 SQL> drop table r2;

Table dropped.

09:07:54 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a

09:07:59 SQL> insert into r1 values(2,'b');

1 row created.

09:08:19 SQL> commit;

Commit complete.

09:08:22 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a

09:08:27 SQL> exec dbms_mview.refresh('mv_r2',method => 'f');
BEGIN dbms_mview.refresh('mv_r2',method => 'f'); END;

*
ERROR at line 1:
ORA-23413: table "JYC"."R1" does not have a materialized view log
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1

09:08:39 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a

09:08:57 SQL> /

ID NAME
---------- ------------------------------
1 a

09:08:59 SQL>
09:09:31 SQL> /

ID NAME
---------- ------------------------------
1 a
2 b

09:09:32 SQL>
09:11:25 SQL>
09:11:25 SQL>
09:11:25 SQL> create materialized view mv_r3 on prebuilt table refresh fast on commit start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1;
create materialized view mv_r3 on prebuilt table refresh fast on commit start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1
*
ERROR at line 1:
ORA-12051: ON COMMIT attribute is incompatible with other options

09:11:26 SQL> create materialized view mv_r3 on prebuilt table refresh fast on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1;
create materialized view mv_r3 on prebuilt table refresh fast on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1
*
ERROR at line 1:
ORA-23413: table "JYC"."R1" does not have a materialized view log

09:11:36 SQL> create materialized view log on r1 with rowid;

Materialized view log created.

09:12:04 SQL> create materialized view mv_r3 on prebuilt table refresh fast on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1;
create materialized view mv_r3 on prebuilt table refresh fast on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1
*
ERROR at line 1:
ORA-12058: materialized view cannot use prebuilt table

09:12:11 SQL> !oerr ora 12058
12058, 00000, "materialized view cannot use prebuilt table"
// *Cause: An attempt was made to use the prebuilt tables.
// *Action: Reissue the SQL command using BUILD IMMEDIATE or BUILD DEFERRED.
//

09:12:21 SQL> !oerr ora 23413
23413, 00000, "table \"%s\".\"%s\" does not have a materialized view log"
// *Cause: The fast refresh can not be performed because the master table
// does not contain a materialized view log.
// *Action: Use the CREATE MATERIALIZED VIEW LOG command to create a
// materialized view log on the master table.
//

09:12:40 SQL>
09:13:45 SQL> create materialized view mv_r3 refresh force on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1;

Materialized view created.

09:13:46 SQL> select * from mv_r3;

ID NAME
---------- ------------------------------
1 a
2 b

09:13:58 SQL> insert into r1 values(3,'c');

1 row created.

09:14:23 SQL> commit;

Commit complete.

09:14:25 SQL>
09:14:26 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a
2 b

09:14:33 SQL> select * from mv_r3;

ID NAME
---------- ------------------------------
1 a
2 b

09:14:40 SQL> /

ID NAME
---------- ------------------------------
1 a
2 b
3 c

09:14:52 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a
2 b

09:14:55 SQL> /

ID NAME
---------- ------------------------------
1 a
2 b

09:14:58 SQL> create materialized view r3 refresh force on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from r1@r2_r1;

Materialized view created.

09:15:09 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a
2 b
3 c

09:15:15 SQL> select * from r3;

ID NAME
---------- ------------------------------
1 a
2 b
3 c

09:15:21 SQL> exec dbms_mview.refresh('r3',method => 'f');

PL/SQL procedure successfully completed.

09:16:23 SQL> exec dbms_mview.refresh('mv_r3',method => 'f');

PL/SQL procedure successfully completed.

09:16:44 SQL> exec dbms_mview.refresh('mv_r2',method => 'f');

PL/SQL procedure successfully completed.

09:16:51 SQL> insert into r1 values(4,'c');

1 row created.

09:17:05 SQL> commit;

Commit complete.

09:17:07 SQL> select * from r3;

ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 c

09:17:10 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a
2 b
3 c

09:17:18 SQL> exec dbms_mview.refresh('mv_r2',method => 'f');

PL/SQL procedure successfully completed.

09:17:22 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 c

09:17:24 SQL> drop materialized view log on r1;

Materialized view log dropped.

09:17:59 SQL> exec dbms_mview.refresh('mv_r2',method => 'f');
BEGIN dbms_mview.refresh('mv_r2',method => 'f'); END;

09:18:03 SQL> exec dbms_mview.refresh('mv_r3',method => 'f');
BEGIN dbms_mview.refresh('mv_r3',method => 'f'); END;

09:18:08 SQL> exec dbms_mview.refresh('r3',method => 'f');
BEGIN dbms_mview.refresh('r3',method => 'f'); END;

09:18:11 SQL> insert into r1 values(4,'c');

1 row created.

09:18:19 SQL> commit;

Commit complete.

09:18:21 SQL> select * from r3;

ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 c

09:18:28 SQL> select * from r1;

ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 c
4 c

09:18:38 SQL> select * from r3;

ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 c

09:18:44 SQL> select * from mv_r2;

ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 c

09:18:53 SQL> select * from mv_r3;

ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 c
4 c

09:19:00 SQL> select * from r3;

ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 c

09:19:04 SQL> /

ID NAME
---------- ------------------------------
1 a
2 b
3 c
4 c
4 c

09:19:19 SQL>

查看全文

http://www.kler.cn/a/309989.html

828华为云征文|Flexus云服务器X实例部署宝塔运维面板

大数据处理技术：分布式文件系统HDFS

基础漏洞——SQL注入原理和利用

职业院校数据科学与大数据技术专业人工智能实训室建设方案

【Linux篇】TCP/IP协议（笔记）

深入探索Go语言中的函数：匿名函数、指针参数与函数返回

vue3中如何拿到vue2中的this

嵌入式epoll面试题面试题及参考答案

金蝶SHR，在列表对某个金额字段汇总展示的需求

英特尔剥离芯片代工业务与亚马逊达成重要合作

五大注入攻击网络安全类型介绍

AutoSar AP 面向服务通信的提供者和消费者“连接”的方法

VUE实现刻度尺进度条

Java-数据结构-优先级队列(堆)-(一) (；´д｀)ゞ

后端id设置long类型时，传到前端，超过19位最后两位为00

k8s用StatefulSet部署redis

图神经网络模型扩展（5）--2

《Effective C++》第三版——让自己习惯C++

SpringDataJPA基础增删改查

【OpenGL 学习笔记】01 - CLion 配置 CMake 运行初始 demo

相关文章：