建立物化視圖時遇見bug 9369183:

做資料庫同步實驗的時候建立prebuilt mv 時 總是提示 ora-32349 ，查詢metalink後，發現這是一個bug。。。。

yang@rac1>create materialized view yangtab2

  2   on prebuilt table refresh fast

  3   as select * from yangtab2@linkyang;

 create materialized view yangtab2

       *

ERROR at line 1:

ORA-32349: cannot reference prebuilt table in definition query for materialized view

yang@rac1>alter table yangtab2 add constraint pk_yangtab2 primary key (id);

Table altered.

create materialized view yangtab2

Bug 9369183: MVIEW WITH PREBUILT TABLE ON SELECT FROM REMOTE TABLE RETURNS ORA-32349     

顯示 Bug 屬性 Bug 屬性

類型     B - Defect     已在産品版本中修複     12.1

嚴重性     2 - Severe Loss of Service     産品版本     11.2.0.1

狀态     80 - Development to Q/A     平台     46 - Linux x86

建立時間     10-Feb-2010     平台版本     NO DATA

更新時間     23-Dec-2010     基本 Bug     6815375

資料庫版本     11.2.0.1         

影響平台     Generic         

産品源     Oracle         

顯示相關産品 相關産品

産品線     Oracle Database Products     系列     Oracle Database

區域     Oracle Database     産品     5 - Oracle Server - Enterprise Edition

Hdr: 9369183 11.2.0.1 RDBMS 11.2.0.1 MATERIAL_VIEW PRODID-5 PORTID-46 6815375

Abstract: MVIEW WITH PREBUILT TABLE ON SELECT FROM REMOTE TABLE RETURNS ORA-32349

*** 02/10/10 08:30 pm ***

  BUG TYPE CHOSEN

  ===============

  Code

  SubComponent: Materialized Views

  ================================

  DETAILED PROBLEM DESCRIPTION

  ============================

  The ability to create an materialized view on prebuilt table with the same

  name as a remote table exists in 9i, 10g, and 11R1, but does not exist in

  11R2, hence the creation of this bug for 11R2.

  Test scenario:  

  Create table as user ABC in 9i database, and as user ABC in 11R2 database

  (tested in 9.2.0.8 and 11.2.0.1):

  CREATE TABLE TEST

  ( ID NUMBER,

  VAL NUMBER,

  VAL2 VARCHAR2(30),

  VAL3 CLOB,

  PRIMARY KEY (ID));

  Create database link (tested private dblink for user ABC, connecting to ABC

  in remote db) from 11R2 to 9i.

  in remote db) from 9i to 11R2.

  In 9i, as user ABC, issue:

  CREATE MATERIALIZED VIEW TEST ON PREBUILT TABLE FOR UPDATE AS SELECT * FROM

  TEST@;

  Materialized view created.

  -  Note that the mview/prebuilt table have the same name/same schema as the

  remote table in select clause of definition query

  -  You can do the above test above in 10g and 11R1, and you will be able to

  successfully create the mview.

  -  Please also note that you can successfully create the mview if the Test

  table exists locally but in a different schema.

  Drop the mview, then do the following: 

  In 11R2, as user ABC, issue:

  TEST@STRM92P

  ERROR at line 1:

  ORA-32349: cannot reference prebuilt table in definition query for

  materialized view

  -  This error is returned for this test in 11R2 only.

  Note that Bug 6815375 addressed an ORA-4020 returned on creation of mview

  with prebuilt table where prebuilt table is referenced as local table in

  definition query.  This bug was resolved with return of ORA-32349 for

  action.  Unfortunately, this error is now returned on mview with prebuilt

  table where prebuilt table is referenced as remote table in definition query

  (where the ORA-4020 did not occur in prior releases). 

  DIAGNOSTIC ANALYSIS

  ===================

  See above 

  WORKAROUND?

  ===========

  Yes 

  WORKAROUND INFORMATION

  ======================

  Create prebuilt table using different name as remote table in definition

  query.  However, for customer of this bug, plan is to drop mview in future

  with resultant change of prebuilt table to regular table - customer wants

  this table to have same name as remote table.  This is a reasonable request,

  as this worked previously without ORA-4020 or other error.

  TECHNICAL IMPACT

  ================

  See above

  RELATED ISSUES (bugs, forums, RFAs)

  ===================================

  Bug 6815375 - ATTEMPT TO CREATE A MV WITH 'ON PREBUILT TABLE' RESULTS IN A

  DEADLOCK

  HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?

  ====================================================

  Always

  DOES THE ISSUE REPRODUCE INTERNALLY?

  ====================================

  IS A TESTCASE AVAILABLE?

  ========================

  Yes