Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
|
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 10th, 2003, 08:36 AM
Registered User
 
Join Date: Oct 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Materialized view problem

When I trie to create the following materialized view:
CREATE MATERIALIZED VIEW F_COUNT_TIME_HQ_BBR_NE_PT_SL
 TABLESPACE ALT_ADSL_DW_T_FCNT_2
 BUILD IMMEDIATE
 USING INDEX TABLESPACE ALT_ADSL_DW_I_FCNT_2
 REFRESH FAST ON COMMIT WITH PRIMARY KEY
 ENABLE QUERY REWRITE
AS
 SELECT TIME_KEY, BBRAS_LOCATION || BBRAS_NAME || BBRAS_SLOT BBRAS_NE_KEY,
   AVG(IN_DISCARDS) IN_DISCARDS, AVG(OUT_DISCARDS) OUT_DISCARDS,
   AVG(IN_OCTETS) IN_OCTETS, AVG(OUT_OCTETS) OUT_OCTETS,
   AVG(LAST_CHANGE) LAST_CHANGE, AVG(AVAILABILITY) AVAILABILITY,
   COUNT(IN_DISCARDS) CNT_IN_DISCARDS, COUNT(OUT_DISCARDS) CNT_OUT_DISCARDS,
   COUNT(IN_OCTETS) CNT_IN_OCTETS, COUNT(OUT_OCTETS) CNT_OUT_OCTETS,
   COUNT(LAST_CHANGE) CNT_LAST_CHANGE, COUNT(AVAILABILITY) CNT_AVAILABILITY,
   COUNT(*) CNT
 FROM F_COUNT_TIME_HQ_BBR_NE_PT, D_BBRAS_NE_PT
 WHERE F_COUNT_TIME_HQ_BBR_NE_PT.BBRAS_NE_KEY = D_BBRAS_NE_PT.BBRAS_NE_KEY
 GROUP BY BBRAS_LOCATION, BBRAS_NAME, BBRAS_SLOT, TIME_KEY
/

I get the error "ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view".

I have created the materialized view logs for the two tables:

CREATE MATERIALIZED VIEW LOG ON D_BBRAS_NE_PT
 TABLESPACE ALT_ADSL_DW_MV_LOG_1
WITH ROWID, SEQUENCE
 (BBRAS_NE_KEY, BBRAS_NE_OID, BBRAS_LOCATION, BBRAS_NAME, BBRAS_SLOT, BBRAS_PORT)
INCLUDING NEW VALUES
/

CREATE MATERIALIZED VIEW LOG ON F_COUNT_TIME_HQ_BBR_NE_PT
 TABLESPACE ALT_ADSL_DW_MV_LOG_1
WITH ROWID, SEQUENCE
 (TIME_KEY, BBRAS_NE_KEY, IN_DISCARDS, OUT_DISCARDS,
 IN_OCTETS, OUT_OCTETS, IN_ERRORS, OUT_ERRORS,
 LAST_CHANGE, AVAILABILITY)
INCLUDING NEW VALUES
/

I know that is an odd materialized view because the bbras_ne_key column is created with the concatenation of three columns... but is what I need.
If I use only one column, bbras_slot for example, it works fine.


Thanks in advance,
Carlos Dias





Similar Threads
Thread Thread Starter Forum Replies Last Post
View state problem rabbit3 ASP.NET 2.0 Professional 0 October 23rd, 2007 08:55 PM
Problem to view in proper oredr. Somesh XSLT 1 March 5th, 2007 08:30 AM
Set operator error durn materialized view creation indupriyav Oracle 0 December 3rd, 2005 08:05 AM
MSTree View - Sync problem JpJoe Access 3 January 31st, 2005 06:19 AM
view server folder problem? cslimcom Classic ASP Databases 4 December 21st, 2004 10:08 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.