Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 10th, 2003, 08:36 AM
Registered User
 
Join Date: Oct 2003
Location: , , Portugal.
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
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:03 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.