Dear List This message has two parts: one is complete offtopic but I feel that it's more important part. However because of the list policy I want to share some Oracle RDBMS related information as well as offtopic information. Steve, I do really appreciate your yesterday's call! Thank you! 1. <OFFTOPIC> I think many of you probably heard about THE TERROR ACT IN BESLAN. Please devote your time and visit this web site http://www.moscowhelp.org/ Thank you! </OFFTOPIC> 2. It's a real life example. Please consider this nested set of materialized views: tbl <- mv01 <- mv02 The developer did not include all required columns into mv01 definition and decided to do it. After the modification of mv01 he gets ORA-12033, he asks why. Thanks. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. SELECT * FROM v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production PL/SQL Release 9.2.0.5.0 - Production CORE 9.2.0.6.0 Production TNS for Linux: Version 9.2.0.5.0 - Production NLSRTL Version 9.2.0.5.0 - Production DROP TABLE tbl; DROP MATERIALIZED VIEW mv01; DROP MATERIALIZED VIEW mv02; CREATE TABLE tbl ( p NUMBER PRIMARY KEY , u NUMBER , g NUMBER , n NUMBER ); CREATE MATERIALIZED VIEW LOG ON tbl WITH ROWID, SEQUENCE (u, n) INCLUDING NEW VALUES / CREATE MATERIALIZED VIEW mv01 REFRESH FAST ON DEMAND WITH ROWID AS SELECT COUNT(u) , COUNT(n) , COUNT(*) , SUM(u) su , SUM(n) sn FROM tbl / CREATE MATERIALIZED VIEW LOG ON mv01 WITH ROWID, SEQUENCE (su, sn) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW mv02 REFRESH FAST ON DEMAND WITH ROWID AS SELECT COUNT(*) , COUNT(sn + su) , AVG(sn + su) FROM mv01 / EXEC dbms_mview.refresh('mv01', 'C'); EXEC dbms_mview.refresh('mv02', 'C'); EXEC dbms_mview.refresh('mv01', 'F'); EXEC dbms_mview.refresh('mv02', 'F'); -- Upgrade (Scenario #1) ALTER MATERIALIZED VIEW LOG ON tbl ADD (p, g); DROP MATERIALIZED VIEW mv01; CREATE MATERIALIZED VIEW mv01 REFRESH FAST ON DEMAND AS SELECT COUNT(p) , COUNT(u) , COUNT(g) , COUNT(n) , COUNT(*) , SUM(p) sp , SUM(u) su , SUM(g) sg , SUM(n) sn FROM tbl / CREATE MATERIALIZED VIEW LOG ON mv01 WITH ROWID, SEQUENCE (sp, su, sg, sn) INCLUDING NEW VALUES; EXEC dbms_mview.refresh('mv01', 'C'); EXEC dbms_mview.refresh('mv02', 'C'); EXEC dbms_mview.refresh('mv01', 'F'); EXEC dbms_mview.refresh('mv02', 'F'); Here one will get ORA-12033 It's not quite obvious why it's raised. It can be explained, though. When a materialized view is created the order of referenced columns (filter mask) is saved in the data dictionary and it's not recomputed when the underlaying mv is recreated. To solve this issue one has to add new columns to the end of mview definition. Consider the upgrade scenario #2: -- p and g are added already see ALTER MATERIALIZED VIEW LOG ON tbl ADD (p, g) DROP MATERIALIZED VIEW mv01; CREATE MATERIALIZED VIEW mv01 REFRESH FAST ON DEMAND AS SELECT COUNT(u) , COUNT(n) , COUNT(*) , SUM(n) sn , SUM(u) su , COUNT(p) , COUNT(g) , SUM(p) sp , SUM(g) sg FROM tbl / CREATE MATERIALIZED VIEW LOG ON mv01 WITH ROWID, SEQUENCE (sp, su, sg, sn) INCLUDING NEW VALUES; EXEC dbms_mview.refresh('mv01', 'C'); EXEC dbms_mview.refresh('mv02', 'C'); EXEC dbms_mview.refresh('mv01', 'F'); EXEC dbms_mview.refresh('mv02', 'F'); -- To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe To search the archives - //www.freelists.org/archives/oracle-l/