oracle 下线表列大小更改时，如何更改物化视图列大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13978796/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What to do to change materialized view column size when under line table column size changed?
提问by magqq
What to do to change materialized view column size when under line table column size changed? This is oracle 11gR2 db on Linux. I tried recompile the MV, it didn't work. Please do not auto migrate this question to another database site, I want to stay here in stackoverflow. Thanks!
下线表列大小更改时，如何更改物化视图列大小？这是 Linux 上的 oracle 11gR2 db。我试过重新编译MV，没有用。请不要将此问题自动迁移到另一个数据库站点，我想留在 stackoverflow 中。谢谢！
回答by Jon Heller
If you alter the table you must also alter the materialized view.
如果更改表，则还必须更改物化视图。
--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
