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

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

article 2024/9/19 4:04:31

利用物化视图同步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;

*
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: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;

*
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:18:03 SQL> exec dbms_mview.refresh('mv_r3',method => 'f');
BEGIN dbms_mview.refresh('mv_r3',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:18:08 SQL> exec dbms_mview.refresh('r3',method => 'f');
BEGIN dbms_mview.refresh('r3',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: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/news/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
  • 【PyTorch入门·求导相关】一文解释 PyTorch的求导 (backward、autograd.grad)
  • C#中抽象类和接⼝有什么区别？
  • 深入解析：联邦政策如何影响科技行业发展
  • windows10通过coursier安装scala
  • 什么是注解？
  • 硬件工程师笔试面试——电机
  • 25. MyBatis中的RowBounds是什么？如何实现内存分页？
  • Presto如何使用
  • CF EDU 169
  • 初写MySQL四张表:(1/4)