Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> materialized view failing to create in 9.2.0.3

materialized view failing to create in 9.2.0.3

From: <Peter.McLarty_at_mincom.com>
Date: Thu, 07 Aug 2003 22:04:23 -0800
Message-ID: <F001.005C98DF.20030807220423@fatcity.com> 





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_at_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. 






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_at_fatcity.com for clarification.

--=_related 001B9EC64A256D7C_=--
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Peter.McLarty_at_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_at_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).



picture





Received on Fri Aug 08 2003 - 01:04:23 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US