遭遇bug9369183，无法建立与远程表同名的prebuilt table物化视图

但是，文档中记录此BUG在11.2.0.2解决，但实际上，在我的测试环境下，即使11.1都没有此问题

记录一下，附加该BUG的具体信息：

[@more@]

Bug 属性

相关产品

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.
  Create database link (tested private dblink for user ABC, connecting to ABC
  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:
  
  CREATE MATERIALIZED VIEW TEST ON PREBUILT TABLE FOR UPDATE AS SELECT * FROM
  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?
  ====================================
  Always
  
  IS A TESTCASE AVAILABLE?
  ========================
  Yes