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: [EMAIL PROTECTED] 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. |  |
