摘自：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> create or replace view v_empdept as
2 select x.empno, x.ename, x.job, y.deptno, y.dname
3 from emp x, dept y
4 where x.deptno = y.deptno
5 /
View created.
SQL> update v_empdept
2 set dname = 'AP_TUNNING'
3 where empno = '7369'
4 /
set dname = 'AP_TUNNING'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> update /*+ bypass_ujvc */ v_empdept
2 set dname = 'AP_TUNNING'
3 where empno = '7369'
4 /
1 row updated.
SQL> commit;
Commit complete.
ODM TEST2:
SQL> select * from v$version
2 ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> update v_empdept
2 set dname = 'AP_TUNNING'
3 where empno = '7369'
4 /
set dname = 'AP_TUNNING'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> update /*+ bypass_ujvc */ v_empdept
2 set dname = 'AP_TUNNING'
3 where empno = '7369'
4 /
set dname = 'AP_TUNNING'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
bypass_ujvc 是内部使用的hint ， 在11.2.0.1以后 bypass_ujvc 被默认忽略
修改 optimizer_features_enable='8.1.5'; 无法 workaround 该问题
SQL> alter session set optimizer_features_enable='8.1.5';
Session altered.
SQL>
SQL> update /*+ bypass_ujvc */ v_empdept
2 set dname = 'AP_TUNNING'
3 where empno = '7369'
4 /
set dname = 'AP_TUNNING'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
在2010年就有类似的bug 被report 但是Oracle开发部门应当是铁了心不恢复这个 hint了
ODM FINDING:
Bug 9595084: /*+ BYPASS_UJVC */ NO LONGER PREVENTS ORA-01779
Hdr: 9595084 11.2.0.1 RDBMS 11.2.0.1 QRY OPTIMIZER PRODID-5 PORTID-226
Abstract: /*+ BYPASS_UJVC */ NO LONGER PREVENTS ORA-1779
PROBLEM:
--------
While on 10.2.0.2 was using /*+ BYPASS_UJVC */ hint in application code to
avoid occurance of Ora-1779 when updating through a view.
Once migration done to 11.2.0.1 the hint no longer is preventing the checks
normally ignored by having the hint set and now Ora-1779 errors occuring.
DIAGNOSTIC ANALYSIS:
--------------------
Testcase was sent.
Tested on:
============
10.2.0.4: update gets 0 rows updated.
11.1.0.7: update gets 0 rows updated.
11.2.0.1: Gets Ora-1779 error.
SQL> update test_v set c2 = 10;
update test_v set c2 = 10
*
ERROR at line 1:
ORA-1779: cannot modify a column which maps to a non key-preserved table
WORKAROUND:
-----------
RELATED BUGS:
-------------
REPRODUCIBILITY:
----------------
Easily reproduced.
11.2.0.1 test performed on:
TEST CASE:
----------
1)Create the testuser.
Create user DNBI_DBA identified by DNBI_DBA
default tablespace users
temporary tablespace temp;
Grant dba to DNBI_DBA;
2)Connect as that user, create 2 tables, and a view that uses the hint, then
query the view
connect DNBI_DBA/DNBI_DBA
create table test1 (c1 number,c2 number);
create table test2 (c1 number,c2 number);
create or replace view test_v as select /*+ BYPASS_UJVC */ a.c2 from
test1 a, (select distinct c1,c2 from test2) b where a.c1 = b.c1 and
a.c1 = 1
/
update test_v set c2 = 10;
STACK TRACE:
------------
SUPPORTING INFORMATION:
-----------------------
Customer has requested sev 1 on this issue for following reasons.
-the application code used uses this hint throughout the code
-this is preventing 10.2.0.2 to 11.2.0.1 migratiuon scheduled for tomorrow
Cannot proceed with migration untl this is resolved.
DIAL-IN INFORMATION:
--------------------
IMPACT DATE:
------------
Migration being prevented is scheduled for tomorrow.
This is at severity 1. A warm-hand off is required and the bug
Should be assigned to the engineer you contact for the warm hand-off.
This may require that you override the BAT assignment.
Failure to perform a warm-hand off may result in a delay in the