oracle – 在行表列大小更改后如何更改实例化视图列大小？

如果在行表列大小更改下更改实例化视图列大小,该怎么办？这是 Linux上的oracle 11gR2 db.我试过重新编译MV,它没有用.请不要将此问题自动迁移到另一个数据库站点,我想留在stackoverflow中.谢谢！

解决方法

--Create simple table and materialized view
create table test1(a varchar2(1 char));
create materialized view mv_test1 as select a from test1;

--Increase column width of column in the table
alter table test1 modify (a varchar2(2 char));

--Insert new value that uses full size
insert into test1 values('12');

--Try to compile and refresh the materialized view
alter materialized view mv_test1 compile;
begin
    dbms_mview.refresh(user||'.MV_TEST1');
end;
/

ORA-12008: error in materialized view refresh path
ORA-12899: value too large for column "JHELLER"."MV_TEST1"."A" (actual: 2,maximum: 1)
ORA-06512: at "SYS.DBMS_SNAPSHOT",line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT",line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT",line 2745
ORA-06512: at line 3

--Increase column width of column in the materialized view and refresh
alter materialized view mv_test1 modify (a varchar2(2 char));
begin
    dbms_mview.refresh(user||'.MV_TEST1');
end;
/
select * from mv_test1;
A
--
12

（编辑：辽源站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!