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

利用物化视图刷新同步表记录

article 2024/11/15 9:08:31

利用物化视图刷新同步表记录，适合目标端只需用来做查询的，并且需要同步的表少，比较灵活。

例如源库表t1,需要同步到目标库t2,t3,t4，测试如下：

SQL> show user;
USER is "JYC"
SQL> create table t1(id int,name varchar2(30));

Table created.

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

Table altered.

SQL> create materialized view log on t1 with primary key;

Materialized view log created.

SQL> create table t2 as select * from t1 where 1=2;

Table created.

SQL> CREATE MATERIALIZED VIEW t2 on prebuilt table refresh fast on commit as select * from t1;

Materialized view created.

SQL> insert into t1 values(1,'A');

1 row created.

SQL> select * from t2;

no rows selected

SQL> commit;

Commit complete.

SQL> select * from t2;

ID NAME
---------- ------------------------------
1 A

SQL> select * from t1;

ID NAME
---------- ------------------------------
1 A

SQL> insert into t1 values(2,'b');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t2;

ID NAME
---------- ------------------------------
1 A
2 b

SQL> set long 99999
SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','T2') from dual;

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','T2')
--------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW "JYC"."T2" ("ID", "NAME")
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FAST ON COMMIT
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT "T1"."ID" "ID","T1"."NAME" "NAME" FROM "T1" "T1"

SQL> select dbms_metadata.get_ddl('TABLE','T2') from dual;

DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------

CREATE TABLE "JYC"."T2"
( "ID" NUMBER(*,0),
"NAME" VARCHAR2(30)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------

SQL> SELECT STALENESS from user_mviews;

STALENESS
-------------------
UNKNOWN

SQL> /

STALENESS
-------------------
UNKNOWN

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

Database link created.

SQL> create table t3 as select * from t1 where 1=2;

Table created.

SQL> alter table t3 add constraint pk_t3 primary key(id) using index;

Table altered.

SQL> CREATE MATERIALIZED VIEW t3 on prebuilt table refresh fast on commit as select * from t1;

Materialized view created.

SQL> select * from t3;

no rows selected

SQL> /

no rows selected

SQL> exec dbms_mview.refresh('T3','c');

PL/SQL procedure successfully completed.

SQL> select * from t3;

ID NAME
---------- ------------------------------
1 A
2 b

SQL> insert into t1 values(3,'c');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t3;

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

SQL> select * from t2;

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

SQL> exec dbms_mview.refresh('T3','f');

PL/SQL procedure successfully completed.

SQL> select * from t3;

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

SQL> insert into t4 values(4,'c');
insert into t4 values(4,'c')
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into t3 values(4,'d');
insert into t3 values(4,'d')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

SQL> insert into t2 values(4,'d');
insert into t2 values(4,'d')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

SQL> insert into t1 values(4,'d');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t3;

ID NAME
---------- ------------------------------
1 A
2 b
3 c
4 d

SQL> select * from t2;

ID NAME
---------- ------------------------------
1 A
2 b
3 c
4 d

SQL> delete from t1 where id=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t2;

ID NAME
---------- ------------------------------
2 b
3 c
4 d

SQL> select * from t3;

ID NAME
---------- ------------------------------
2 b
3 c
4 d

SQL> update t1 set name='a' where id=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t2;

ID NAME
---------- ------------------------------
2 b
4 d
3 a

SQL> select * from t3;

ID NAME
---------- ------------------------------
3 a
2 b
4 d

SQL> drop table t3;
drop table t3
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "JYC"."T3"

SQL> DROP MATERIALIZED VIEW t3;

Materialized view dropped.

SQL> select * from t3;

ID NAME
---------- ------------------------------
3 a
2 b
4 d

SQL> drop table t3;

Table dropped.

SQL> create table t3 as select * from t1 where 1=2;

Table created.

SQL> alter table t3 add constraint pk_t3 primary key(id) using index;

Table altered.

SQL> CREATE MATERIALIZED VIEW t3 on prebuilt table refresh fast on commit as select * from t1@t1_t3;
CREATE MATERIALIZED VIEW t3 on prebuilt table refresh fast on commit as select * from t1@t1_t3
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

SQL> select * from t1@t1_t3;

ID NAME
---------- ------------------------------
2 b
3 a
4 d

SQL> !oerr ora 12054
12054, 00000, "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.
//

SQL> CREATE MATERIALIZED VIEW t3 on prebuilt table refresh fast on demand as select * from t1@t1_t3;

Materialized view created.

SQL> select * from t3;

no rows selected

SQL> exec dbms_mview.refresh('T3','f');

PL/SQL procedure successfully completed.

SQL> select * from t3;

no rows selected

SQL> exec dbms_mview.refresh('T3','c');

PL/SQL procedure successfully completed.

SQL> select * from t3;

ID NAME
---------- ------------------------------
2 b
3 a
4 d

SQL> insert into t1 values(5,'e');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t2;

ID NAME
---------- ------------------------------
2 b
4 d
3 a
5 e

SQL> select * from t3;

ID NAME
---------- ------------------------------
2 b
3 a
4 d

SQL> exec dbms_mview.refresh('T3','f');

PL/SQL procedure successfully completed.

SQL> select * from t3;

ID NAME
---------- ------------------------------
2 b
3 a
4 d
5 e

SQL> create table t4 as select * from t1 where 1=2;

Table created.

SQL> alter table t4 add constraint pk_t4 primary key(id) using index;

Table altered.

SQL> CREATE MATERIALIZED VIEW t4 on prebuilt table refresh fast as select * from t1@t1_t3;

Materialized view created.

SQL> select * from t4;

no rows selected

SQL> exec dbms_mview.refresh('T4','f');

PL/SQL procedure successfully completed.

SQL> select * from t4;

no rows selected

SQL> exec dbms_mview.refresh('T4','c');

PL/SQL procedure successfully completed.

SQL> select * from t4;

ID NAME
---------- ------------------------------
2 b
3 a
4 d
5 e

SQL> insert into t1 values(6,'f');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t4;

ID NAME
---------- ------------------------------
2 b
3 a
4 d
5 e

SQL> exec dbms_mview.refresh('T4','f');

PL/SQL procedure successfully completed.

SQL> select * from t4;

ID NAME
---------- ------------------------------
2 b
3 a
4 d
5 e
6 f

SQL> select * from t3;

ID NAME
---------- ------------------------------
2 b
3 a
4 d
5 e

SQL> exec dbms_mview.refresh('T3','f');

PL/SQL procedure successfully completed.

SQL> select * from t3;

ID NAME
---------- ------------------------------
2 b
3 a
4 d
5 e
6 f

SQL>
SQL> --设定刷新任务T4表，每分钟刷新一次
SQL>
SQL> declare jobid number;
2 begin
3 sys.dbms_job.submit(job => jobid,
4 what => 'dbms_mview.refresh(''T4'');',
5 next_date => sysdate,
6 interval => 'sysdate+1/1440');
7 commit;
8 end;
9 /

PL/SQL procedure successfully completed.

--设定job任务

declare jobid number;
 begin
 sys.dbms_job.submit(job => jobid,
 what => 'dbms_mview.refresh(''T4'');',
 next_date => sysdate,
 interval => 'sysdate+1/1440');
 commit;
 end;
/

SQL> set time on
15:16:12 SQL> insert into t1 values(1,'a');

1 row created.

15:16:25 SQL> commit;

Commit complete.

15:16:27 SQL>
15:16:28 SQL> select * from t4;

ID NAME
---------- ------------------------------
2 b
3 a
4 d
5 e
6 f

15:16:37 SQL> /

ID NAME
---------- ------------------------------
2 b
3 a
4 d
5 e
6 f

15:16:52 SQL> /

ID NAME
---------- ------------------------------
2 b
3 a
4 d
5 e
6 f

15:17:02 SQL> /

ID NAME
---------- ------------------------------
1 a
2 b
3 a
4 d
5 e
6 f

6 rows selected.

15:17:26 SQL> select * from t4;

ID NAME
---------- ------------------------------
1 a
2 b
3 a
4 d
5 e
6 f

6 rows selected.

15:17:38 SQL> select * from t3;

ID NAME
---------- ------------------------------
2 b
3 a
4 d
5 e
6 f

15:17:42 SQL>

15:17:42 SQL>
15:28:31 SQL> create table t5 as select * from t1 where 1=2;

Table created.

15:28:45 SQL> alter table t5 add constraint pk_t5 primary key(id) using index;

Table altered.

15:29:04 SQL> create materialized view t5 refresh force on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from t1@t1_t3;
create materialized view t5 refresh force on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from t1@t1_t3
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

15:30:41 SQL> !oerr ora 955
00955, 00000, "name is already used by an existing object"
// *Cause:
// *Action:

15:30:53 SQL>
15:31:59 SQL> create materialized view t5 on prebuilt table refresh force on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from t1@t1_t3;
create materialized view t5 on prebuilt table refresh force on demand start with sysdate next sysdate+1/1440 with rowid enable query rewrite as select * from t1@t1_t3
*
ERROR at line 1:
ORA-12058: materialized view cannot use prebuilt table

15:32:01 SQL>
15:32:40 SQL>
15:32:40 SQL> create materialized view t5 refresh force on demand start with sysdate next sysdate+1/1440 as select * from t1@t1_t3;
create materialized view t5 refresh force on demand start with sysdate next sysdate+1/1440 as select * from t1@t1_t3
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

15:32:41 SQL>
15:33:27 SQL> create materialized view t5 on prebuilt table refresh fast on demand start with sysdate next sysdate+1/1440 as select * from t1@t1_t3;

Materialized view created.

15:33:28 SQL> select * from t5;

no rows selected

15:33:35 SQL> /

no rows selected

15:33:44 SQL> /

no rows selected

15:33:57 SQL> insert into t1 values(7,'g');

1 row created.

15:34:17 SQL> commit;

Commit complete.

15:34:19 SQL> select * from t5;