Materialized view upgrade: avoding ORA-12033

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_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/