Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h787YOc04597 for ; Fri, 8 Aug 2003 02:34:24 -0500 X-ClientAddr: 66.27.56.212 Received: from www3.fatcity.com (rrcs-west-66-27-56-212.biz.rr.com [66.27.56.212]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h787Y2p04588 for ; Fri, 8 Aug 2003 02:34:03 -0500 Received: (from root@localhost) by www3.fatcity.com (8.11.6/8.11.6) id h785AaU17169 for oracle-l@orafaq.net; Thu, 7 Aug 2003 22:10:36 -0700 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005C98DF; Thu, 07 Aug 2003 22:04:23 -0800 Message-ID: Date: Thu, 07 Aug 2003 22:04:23 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Peter.McLarty@mincom.com Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Peter.McLarty@mincom.com Subject: materialized view failing to create in 9.2.0.3 Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/related; boundary="=_related 001B9EC64A256D7C_=" --=_related 001B9EC64A256D7C_= Content-Type: multipart/alternative; boundary="=_alternative 001B9EC94A256D7C_=" --=_alternative 001B9EC94A256D7C_= Content-Type: text/plain; charset="us-ascii" Hi Mview gurus I have tried to create a materialized view with the following characteristics. If I have something wrong with my design that will avoid this i would like to know but I suspect that it is a bug of sorts. Can anyone provide some useful input Sorry for the long email CREATE MATERIALIZED VIEW LOG ON "EP"."EP_CURVEDETAILS" TABLESPACE "EP" PCTFREE 60 PCTUSED 0 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) WITH ROWID, PRIMARY KEY ( CURVEDETAILS_DATE, CURVE_ID) EXCLUDING NEW VALUES CREATE MATERIALIZED VIEW "EP"."EP_MV_CURVEPERIOD_VALUES" TABLESPACE "EP" BUILD IMMEDIATE USING INDEX TABLESPACE "EP" REFRESH FAST ON COMMIT AS SELECT curvedetails_id, curve_id, curvedetails_date, 'P0030' AS P_No, P0030 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P0100' AS P_No, P0100 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P0130' AS P_No, P0130 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0200' AS P_No, P0200 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0230' AS P_No, P0230 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P0300' AS P_No, P0300 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P0330' AS P_No, P0330 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P0400'AS P_No, P0400 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0430'AS P_No, P0430 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0500'AS P_No, P0500 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0530'AS P_No, P0530 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0600'AS P_No, P0600 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0630'AS P_No, P0630 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0700'AS P_No, P0700 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0730'AS P_No, P0730 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0800'AS P_No, P0800 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0830'AS P_No, P0830 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0900'AS P_No, P0900 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0930'AS P_No, P0930 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1000'AS P_No, P1000 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1030'AS P_No, P1030 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1100'AS P_No, P1100 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1130'AS P_No, P1130 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1200'AS P_No, P1200 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1230'AS P_No, P1230 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1300'AS P_No, P1300 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1330'AS P_No, P1330 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1400'AS P_No, P1400 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1430'AS P_No, P1430 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1500'AS P_No, P1500 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1530'AS P_No, P1530 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1600'AS P_No, P1600 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1630'AS P_No, P1630 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1700'AS P_No, P1700 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1730'AS P_No, P1730 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1800'AS P_No, P1800 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1830'AS P_No, P1830 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1900'AS P_No, P1900 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1930'AS P_No, P1930 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P2000'AS P_No, P2000 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P2030'AS P_No, P2030 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P2100'AS P_No, P2100 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P2130'AS P_No, P2130 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P2200'AS P_No, P2200 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P2230'AS P_No, P2230 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P2300'AS P_No, P2300 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P2330'AS P_No, P2330 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P2400'AS P_No, P2400 AS P_Reading FROM EP_CURVEDETAILS order BY curvedetails_id, curve_id, curvedetails_date, p_no This cause the instance to shutdown of the Oracle instance under windows and causes a disconnect ( end of communication error)under Solaris both DB's9.2.0.3. I havent raised a tar as yet so if anyone can tell me my query is wrong or invalid for a mview or other useful info else I shall raise a TAR Cheers -- ================================================= Peter McLarty E-mail: Peter.Mclarty@mincom.com Technical Consultant WWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, Australia Mobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 ================================================= "If people did not sometimes do silly things, nothing intelligent would ever get done." - Ludwig Wittgenstein ================================================= Mincom "The People, The Experience, The Vision" ================================================= This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. --=_alternative 001B9EC94A256D7C_= Content-Type: text/html; charset="us-ascii"
Hi Mview gurus
I have tried to create a materialized view with the following characteristics. If I have something wrong with my design that will avoid this i would like to know but I suspect that it is a bug of sorts.

Can anyone provide some useful input
Sorry for the long email

CREATE MATERIALIZED VIEW LOG
    ON "EP"."EP_CURVEDETAILS"
TABLESPACE "EP" PCTFREE 60 PCTUSED 0 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
WITH ROWID, PRIMARY KEY ( CURVEDETAILS_DATE, CURVE_ID)
EXCLUDING NEW VALUES


CREATE MATERIALIZED VIEW "EP"."EP_MV_CURVEPERIOD_VALUES"
TABLESPACE "EP"
BUILD IMMEDIATE  
USING INDEX
TABLESPACE "EP"  
REFRESH FAST
ON COMMIT
AS
SELECT curvedetails_id, curve_id, curvedetails_date, 'P0030' AS P_No, P0030 AS P_Reading
    FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P0100' AS P_No, P0100 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P0130' AS P_No, P0130 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0200' AS P_No, P0200 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0230' AS P_No, P0230 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P0300' AS P_No, P0300 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P0330' AS P_No, P0330 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P0400'AS P_No, P0400 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0430'AS P_No, P0430 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0500'AS P_No, P0500 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0530'AS P_No, P0530 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0600'AS P_No, P0600 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0630'AS P_No, P0630 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0700'AS P_No, P0700 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0730'AS P_No, P0730 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0800'AS P_No, P0800 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0830'AS P_No, P0830 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0900'AS P_No, P0900 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P0930'AS P_No, P0930 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1000'AS P_No, P1000 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1030'AS P_No, P1030 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1100'AS P_No, P1100 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1130'AS P_No, P1130 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P1200'AS P_No, P1200 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P1230'AS P_No, P1230 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P1300'AS P_No, P1300 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P1330'AS P_No, P1330 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P1400'AS P_No, P1400 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P1430'AS P_No, P1430 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id, curvedetails_date, 'P1500'AS P_No, P1500 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1530'AS P_No, P1530 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1600'AS P_No, P1600 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1630'AS P_No, P1630 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1700'AS P_No, P1700 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1730'AS P_No, P1730 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1800'AS P_No, P1800 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1830'AS P_No, P1830 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1900'AS P_No, P1900 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P1930'AS P_No, P1930 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P2000'AS P_No, P2000 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P2030'AS P_No, P2030 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P2100'AS P_No, P2100 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P2130'AS P_No, P2130 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P2200'AS P_No, P2200 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P2230'AS P_No, P2230 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P2300'AS P_No, P2300 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P2330'AS P_No, P2330 AS P_Reading
     FROM EP_CURVEDETAILS
    UNION ALL
    SELECT curvedetails_id, curve_id,curvedetails_date, 'P2400'AS P_No, P2400 AS P_Reading
     FROM EP_CURVEDETAILS
    order BY curvedetails_id, curve_id, curvedetails_date, p_no


This cause the instance to shutdown of the Oracle instance under windows and causes a disconnect ( end of communication error)under Solaris both DB's9.2.0.3.

I havent raised a tar as yet so if anyone can tell me my query is wrong or invalid for a mview or other useful info else I shall raise a TAR



Cheers



--
=================================================
Peter McLarty               E-mail: Peter.Mclarty@mincom.com
Technical Consultant        WWW: http://www.mincom.com
APAC Technical Services     Phone: +61 (0)7 3303 3461
Brisbane,  Australia        Mobile: +61 (0)402 094 238
                           Facsimile: +61 (0)7 3303 3048
=================================================
"If people did not sometimes do silly things, nothing intelligent would ever
get done."
  - Ludwig Wittgenstein
=================================================
Mincom "The People, The Experience, The Vision"

=================================================
This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. 		mc_stackedStaT4x3.jpg - 4289 Bytes

--=_alternative 001B9EC94A256D7C_=-- --=_related 001B9EC64A256D7C_= Content-Type: image/jpeg Content-ID: <_2_223C00002D30001B9EB94A256D7C> Content-Transfer-Encoding: 7bit --=_related 001B9EC64A256D7C_=-- Content-Type: text/plain; name="ReadMe.txt"; charset="us-ascii" Content-Transfer-Encoding: 7bit The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster@fatcity.com for clarification. --=_related 001B9EC64A256D7C_=-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Peter.McLarty@mincom.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).