test,基于预定义表的物化视图是否可以加字段

目录

• 1. 创建物化视图日志
• 2. 基于原表创建目标表
• 3. 基于预定义表创建物化视图
• 4. 创建日志表和触发器
• 5. 测试结果
  • 5.1 删除mv对预定义表和数据不会有影响
  • 5.2 重建mv会清空源端物化视图日志

物化视图很方便，能通过外加触发器的方式记录表的变化情况。
但碰到原表新增字段的情况就无能为力了，无法在触发器正常工作的情况下加字段。
本来要测试一下，基于预定义的表是否可以直接加字段，测试结果是不可以。

测试结果：

1. 删除mv，预定义表不会一起删除，数据也不会变化
2. 重建mv会清空源端物化视图日志，所以源端如果新增了字段，不能通过重建mv的方式既保留增量变化又新增字段。

• 数据库版本 Oracle 11.2.0.4

1. 创建物化视图日志

DROP MATERIALIZED VIEW LOG ON emp_source ; 

CREATE MATERIALIZED VIEW LOG ON emp_source WITH PRIMARY KEY ; 

--创建dblink，物化视图是基于dblink的
CREATE DATABASE LINK to_scott CONNECT TO scott IDENTIFIED BY tiger USING '192.168.109.191/bidbdev'; 

2. 基于原表创建目标表

DROP MATERIALIZED VIEW mv_emp_source ; 
DROP TABLE mv_emp_source PURGE ; 
Create Table mv_emp_source As select * from emp_source@to_scott ; 
ALTER TABLE mv_emp_source ADD CONSTRAINT pk_mvemp_empno   PRIMARY KEY   (empno); 

3. 基于预定义表创建物化视图

创建物化视图之后，默认不会刷新， 要全量刷新一次。

CREATE MATERIALIZED VIEW mv_emp_source
ON PREBUILT TABLE 
REFRESH FORCE
NEXT NULL 
AS
select * from emp_source@to_scott ;


BEGIN
  dbms_mview.refresh(list => 'mv_emp_source');
END;
/

4. 创建日志表和触发器

--创建business_log
-- Create table
create table BUSINESS_LOG
(
  datecode       DATE not null,
  tablename      VARCHAR2(40) not null,
  businessfield1 VARCHAR2(20) not null,
  field1_content VARCHAR2(300) not null,
  businessfield2 VARCHAR2(20),
  field2_content VARCHAR2(300),
  businessfield3 VARCHAR2(20),
  field3_content VARCHAR2(300),
  businessfield4 VARCHAR2(20),
  field4_content VARCHAR2(300),
  businessfield5 VARCHAR2(20),
  field5_content VARCHAR2(300),
  businessfield6 VARCHAR2(20),
  field6_content VARCHAR2(300),
  businessfield7 VARCHAR2(20),
  field7_content VARCHAR2(300),
  businessfield8 VARCHAR2(20),
  field8_content VARCHAR2(300),
  businesstype   VARCHAR2(10) not null,
  sourceflag     CHAR(1),
  remark         VARCHAR2(20)
);

-- Create table
create table BUSINESS_DEL_LOG
(
  datecode       DATE not null,
  tablename      VARCHAR2(40) not null,
  businessfield1 VARCHAR2(20) not null,
  field1_content VARCHAR2(300) not null,
  businessfield2 VARCHAR2(20),
  field2_content VARCHAR2(300),
  businessfield3 VARCHAR2(20),
  field3_content VARCHAR2(300),
  businessfield4 VARCHAR2(20),
  field4_content VARCHAR2(300),
  businessfield5 VARCHAR2(20),
  field5_content VARCHAR2(300),
  businessfield6 VARCHAR2(20),
  field6_content VARCHAR2(300),
  businessfield7 VARCHAR2(20),
  field7_content VARCHAR2(300),
  businessfield8 VARCHAR2(20),
  field8_content VARCHAR2(300),
  businesstype   VARCHAR2(10) not null,
  sourceflag     CHAR(1),
  remark         VARCHAR2(20)
)
;


CREATE OR REPLACE TRIGGER trigger_mv_emp_source_del
  before delete on mv_emp_source for each row
begin
  --delete操作记录
  insert into Business_Del_Log
    (BusinessType,
     DateCode,
     TableName,
     BusinessField1,
     Field1_Content,
     SourceFlag,
     Remark)
  values
    ('DEL',
     sysdate,
     'MV_EMP_SOURCE',
     'EMPNO',
     nvl(:OLD.EMPNO, 1),
     '1',
     NULL);
end;


CREATE OR REPLACE TRIGGER trigger_mv_emp_source
  before update or insert on mv_emp_source  for each row
begin
  --update操作记录
  insert into Business_Log
    (BusinessType,
     DateCode,
     TableName,
     BusinessField1,
     Field1_Content,
     SourceFlag,
     Remark)
  values
    ('OLD',
     sysdate,
     'MV_EMP_SOURCE',
     'EMPNO',
     nvl(:OLD.EMPNO, 1),
     '1',
     NULL);

  --insert操作记录
  insert into Business_Log
    (BusinessType,
     DateCode,
     TableName,
     BusinessField1,
     Field1_Content,
     SourceFlag,
     Remark)
  values
    ('NEW',
     sysdate,
     'MV_EMP_SOURCE',
     'EMPNO',
     nvl(:OLD.EMPNO, 1),
     '1',
     NULL);
end;

5. 测试结果

5.1 删除mv对预定义表和数据不会有影响

drop MATERIALIZED VIEW mv_emp_source; 

5.2 重建mv会清空源端物化视图日志

select * from mlog$_emp_source a  ;