物化视图添加删除列测试

materialized view add column

1，新建测试
create table css (a int ,b varchar2(10));
insert into css values( 1, 'a');
commit;

2，创建物化视图
CREATE MATERIALIZED VIEW LOG ON css WITH ROWID;

CREATE MATERIALIZED VIEW mv_css
REFRESH FAST ON DEMAND
WITH ROWID
START WITH TO_DATE('21-03-2024 12:00:00', 'DD-MM-YYYY HH24:MI:SS') NEXT /1:HRS/ SYSDATE + 1/(2460)
AS
SELECT FROM css;
exec DBMS_MVIEW.REFRESH('mv_css');

-- 查询物化视图。
select from mv_css;
SQL> select from mv_css;

 A B

 1 a

-- 手动执行刷新物化视图
--select job,what from all_jobs;
--exec dbms_job.run(3);

exec DBMS_MVIEW.REFRESH('mv_css');

1，表添加列，化物视图不会自动刷新，新添加列，物化视图新添加不能查询。
alter table css add (c int);
insert into css values( 2, 'b',2);
commit;
exec DBMS_MVIEW.REFRESH('mv_css');
select from css;
SQL> select from css;

 A B               C

 1 a
 2 b               2

select * from mv_css;
SQL>

 A B

 1 a
 2 b

2，删除已经存在的删除列，导致会报错，物化视图刷新失败。
alter table css drop (b);

exec DBMS_MVIEW.REFRESH('mv_css');

ORA-12012: error on auto execute of job 3
ORA-12008: error in materialized view refresh path
ORA-00904: "CSS"."B": invalid identifier <<<<<<<<< B列缺失导致刷新失败。
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_IREFRESH", line 689
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
SESSION ID:(27.56019) 2024-03-21 12:17:33.329 SERVICE NAME:(SYS$USERS) 2024-03-21 12:17:33.329

3，更改列长度,会导致视图变为error,需要重建。
drop MATERIALIZED VIEW LOG ON css;
drop MATERIALIZED VIEW mv_css;
drop table css;

alter table css modify b varchar2(100);
exec DBMS_MVIEW.REFRESH('mv_css');
select compile_state from user_mviews where mview_name ='MV_CSS';

COMPILE_STATE

COMPILATION_ERROR

1,表增加列对物化视图的刷新没有影响,但增加的新列不能在物化视图中显示,需要重建物化视图．
2,表删除列会导致物化视图刷新失败，但对增列的删除不受影响．
3,更改列长度,会导致视图变为error,需要重建。
4,ALTER MATERIALIZED VIEW LOG :语句来更改现有物化视图日志的存储特征或类型。

#

#

ALTER MATERIALIZED VIEW LOG :语句来更改现有物化视图日志的存储特征或类型。

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-MATERIALIZED-VIEW-LOG.html#GUID-4DAD5E6F-E30A-43D0-B023-634752E0E627

ALTER MATERIALIZED VIEW LOG ON css add (c) ;
ALTER MATERIALIZED VIEW LOG ON css drop (b) ;
alter table css add (b varchar2(10));
select compile_state from user_mviews where mview_name ='MV_CSS';
alter materialized view MV_CSS compile;
select compile_state from user_mviews where mview_name ='MV_CSS';

alter materialized view mv_test_mv add c int;

drop materialized view mv_css; <<< 需要手动删除，重建。
CREATE MATERIALIZED VIEW mv_css
REFRESH FAST ON DEMAND
WITH ROWID
START WITH TO_DATE('21-03-2024 12:00:00', 'DD-MM-YYYY HH24:MI:SS') NEXT /1:HRS/ SYSDATE + 1/(2460)
AS
SELECT FROM css;