Bug 9369183 - ORA-32349 creating MView on prebuilt table using select from remote table
遭遇bug9369183,無法建立與遠端表同名的prebuilt table物化檢視
但是,文件中記錄此BUG在11.2.0.2解決,但實際上,在我的測試環境下,即使11.1都沒有此問題
記錄一下,附加該BUG的具體資訊:
[@more@]Bug 9369183: MVIEW WITH PREBUILT TABLE ON SELECT FROM REMOTE TABLE RETURNS ORA-32349 | |||||
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 |
更新時間 | 30-Jul-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.
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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/19423/viewspace-1038964/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mview on prebuilt table用法嘗試ViewUI
- Recipe 6.10. Creating a Delimited List from Table RowsMIT
- 關於 mysql 中的 select * from table_a,table_b 的問題MySql
- 使用 on prebuilt table 建立物化檢視 (ZT)UI
- 使用 on prebuilt table 建立物化檢視(zt)UI
- 使用prebuilt table 方式遷移資料UI
- create a partition table using a exsit table
- Oracle table selectOracle
- 建立DBMS_MVIEW.EXPLAIN_MVIEW分析需要表MV_CAPABILITIES_TABLEViewAI
- alter table using indexIndex
- Oracle Create Table as SelectOracle
- sqlserver不能直接create table as select ......SQLServer
- Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231AI
- Creating a Physical Standby using RMAN DUPLICATE FROM ACTIVE DATABASEDatabase
- GoldenGate "Error mapping from table.a to table.a"錯誤測試GoErrorAPP
- CREATE TABLE AS SELECT(CAST)(二)-ConceptAST
- DML對prebuilt table的內容修改時出現ORA-01732UI
- How to Quickly Create a Copy of a Table using Transact-SQLUISQL
- mysql partition table use to_days bugMySql
- You can‘t specify target table ‘Person‘ for update in FROM clause
- Oracle模擬MySQL的show index from table命令OracleMySqlIndex
- Recipe 4.10. Updating with Values from Another Table
- Bootstrap select2 ,table, 清空表單formbootORM
- 【MySQL】select for update 的Row Lock 與Table LockMySql
- CREATE TABLE AS SELECT(CAST)(一)-ORA-1652AST
- Multi-table insert using pipelined functionFunction
- Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBAExcel
- 拆分Table 為Partition Table
- mysql中You can’t specify target table for update in FROM clMySql
- Error: cannot fetch last explain plan from PLAN_TABLEErrorASTAI
- [Oracle] Partition table exchange Heap tableOracle
- html~table、table cell的使用HTML
- 如何修改table及partitions Table
- table
- MysqL中的Show Index From Table_Name命令說明MySqlIndex
- Script: To remove Chained Rows from a Table (Doc ID 1019556.6)REMAI
- User defined table type and table valued parameters
- Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE WithoutGUIIDEDatabase