ORA-12028 errors on REFRESH FAST
- From: Lou Avrami <avramil@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 11 Nov 2009 21:10:54 -0500 (EST)
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@xxxxxxxxxxxxxx
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"@FM1_S_LINK "RECENT_ALERTSS";
FROM "FM1"."RECENT_ALERTSS"@FM1_S_LINK "RECENT_ALERTSS"
*
ERROR at line 36:
ORA-12028: materialized view type is not supported by master site
@FM1_S_LINK.NATAPPSUNIX.ENG.COM
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » ORA-12028 errors on REFRESH FAST - Lou Avrami
