ORA-12028 errors on REFRESH FAST

From: Lou Avrami <avramil_at_concentric.net>
Date: Wed, 11 Nov 2009 21:10:54 -0500 (EST)
Message-Id: <20091112021055.1A6001E28_at_alexander.cnc.net>

Hi all,

We are receiving ORA-12028 errors when trying to create REFRESH FAST materialized views in an 11.1.0.7 database.

This is a new data warehouse-type of database, taking the place of an old 9.2.0.8 database.

One of the applications has several materialized views which pull data from 9.2.0.8 and 10.2.0.4 databases over database links.

The current production target database is 9.2.0.8, NLS characterset US7ASCII. One database from where data is being pulled is 10.2.0.4, NLS characterset AL32UTF8. Two databases are 9.2.0.8, NLS characterset WE8ISO8859P1.

The REFRESH FAST materialized have been running in the old 9i database for over a year.

The new target database is 11.1.0.7, NLS characterset AL32UTF8.

When trying to create the REFRESH FAST materialized views in the new 11g database, ORA-12028 errors are received.

Per MetaLink Note 259506.1, I converted the new 11g database from AL32UTF8 to UTF8.

When I then tried to create the REFRESH FAST materialized views, I still received ORA-12028 errors.

Would anyone have any troubleshooting suggestions?

Thanks,
Lou Avrami
avramil_at_concentric.net

Below is a sample error from the run after the conversion to UTF8.

NADWP : SYS : SQL> CREATE MATERIALIZED VIEW "FM1"."MV_RECENT_ALERTSS"   2 COMPRESS
  3 TABLESPACE "SOUTH_DEFAULT"
  4 BUILD IMMEDIATE
  5 USING INDEX TABLESPACE "FM1_INDEX"   6 REFRESH FAST ON DEMAND
  7 WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT   8 DISABLE QUERY REWRITE
  9 AS SELECT

 10    "RECENT_ALERTSS"."SEQ_NO" "SEQ_NO",
 11    "RECENT_ALERTSS"."UPDATE_DATE" "UPDATE_DATE",
 12    "RECENT_ALERTSS"."ALERT_NO" "ALERT_NO",
 13    "RECENT_ALERTSS"."FIRST_ONE" "FIRST_ONE",
 14    "RECENT_ALERTSS"."LAST_ONE" "LAST_ONE",
 15    "RECENT_ALERTSS"."ACK_DATE" "ACK_DATE",
 16    "RECENT_ALERTSS"."CLEARED" "CLEARED",
 17    "RECENT_ALERTSS"."COUNT" "COUNT",
 18    "RECENT_ALERTSS"."SEVERITY" "SEVERITY",
 19    "RECENT_ALERTSS"."DESCRIPTION" "DESCRIPTION",
 20    "RECENT_ALERTSS"."CLEARREASON" "CLEARREASON",
 21    "RECENT_ALERTSS"."ALERT_NAME" "ALERT_NAME",
 22    "RECENT_ALERTSS"."MO" "MO",
 23    "RECENT_ALERTSS"."MANAGER" "MANAGER",
 24    "RECENT_ALERTSS"."ACK_OPER" "ACK_OPER",
 25    "RECENT_ALERTSS"."MANAGER_CLASS" "MANAGER_CLASS",
 26    "RECENT_ALERTSS"."MO_CLASS" "MO_CLASS",
 27    "RECENT_ALERTSS"."SITEID" "SITEID",
 28    "RECENT_ALERTSS"."PORTID" "PORTID",
 29    "RECENT_ALERTSS"."TICKETID" "TICKETID",
 30    "RECENT_ALERTSS"."CLEARED_BY" "CLEARED_BY",
 31    "RECENT_ALERTSS"."VIEWTYPE" "VIEWTYPE",
 32    "RECENT_ALERTSS"."OLA" "OLA",
 33    "RECENT_ALERTSS"."PARENT_IND" "PARENT_IND",
 34    "RECENT_ALERTSS"."CHILD_IND" "CHILD_IND",
 35    "RECENT_ALERTSS"."FORWARDING_SYS" "FORWARDING_SYS"

 36 FROM "FM1"."RECENT_ALERTSS"_at_FM1_S_LINK "RECENT_ALERTSS"; FROM "FM1"."RECENT_ALERTSS"_at_FM1_S_LINK "RECENT_ALERTSS"

                            *

ERROR at line 36:
ORA-12028: materialized view type is not supported by master site _at_FM1_S_LINK.NATAPPSUNIX.ENG.COM

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 11 2009 - 20:10:54 CST