oracle中升级数据库到10.2.0.5遭遇ORA-00918: column ambiguously defined
一个数据库从10201升级到10205之后,出现ORA-00918错误,查询mos发现在以前版本中是bug,Oracle好像在10205中把它修复了,结果就是以前应用的sql无法正常执行.这次升级的结果就是客户晚上3点联系开发商紧急修改程序。再次提醒：再小的系统数据库升级都需要做,功能测试,SPA测试,确保升级后功能和性能都正常.
SQL> select * from v$version;
执行报错ORA-00918
多个表JOIN连接,由于在select中的列未指定表名，而且该列在多个表中有，因此在10205中报ORA-00918错误，Oracle认为在以前的版本中是 Bug 5368296: SQL NOT GENERATING ORA-918 WHEN USING JOIN. 升级到10.2.0.5, 11.1.0.7 and 11.2.0.2版本，需要注意此类问题。修复bug没事,但是修复了之后导致系统需要修改sql才能够运行,确实让人很无语
SQL> set autot trace
SQL> set lines 100
SQL> SELECT yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx,
2 item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh,
3 TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb, zyh,ch,xm,
4 bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
5 sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy,
6 lb_yp_yl, zsq_code
7 FROM op.yz a LEFT OUTER JOIN op.pc b
8 ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), ' ')
9 LEFT JOIN op.zy p ON a.zyh = p.zyh
10 WHERE p.cy='在院' AND p.new_patient='1'
11 AND upper(nvl(p.bj,1))<> 'Y'
12 AND (state = '已核对')
13 AND is_in_bill IS NULL
14 ORDER BY ksrq, yz_id ;
bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
*
ERROR at line 4:
ORA-00918: column ambiguously defined
SQL> select COLUMN_NAME,TABLE_NAME from DBA_tab_columns where column_name='BQ'
2 AND TABLE_NAME IN('YZ','ZY','PC');
COLUMN_NAME TABLE_NAME
------------------------------ ------------------------------
BQ ZY
BQ YZ
10.2.0.1中执行正常
E:＼>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 1月 3 14:09:51 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> set autot trace
SQL> set lines 100
SQL> SELECT yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx,
2 item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh,
3 TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb, zyh,ch,xm
,
4 bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
5 sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy,
6 lb_yp_yl, zsq_code
7 FROM op.yz a LEFT OUTER JOIN op.pc b
8 ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), '
')
9 LEFT JOIN op.zy p ON a.zyh = p.zyh
10 WHERE p.cy='在院' AND p.new_patient='1'
11 AND upper(nvl(p.bj,1))<> 'Y'
12 AND (state = '已核对')
13 AND is_in_bill IS NULL
14 ORDER BY ksrq, yz_id ;
已选择19804行。
执行计划
----------------------------------------------------------
ERROR:
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-16000: 打开数据库以进行只读访问
SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
41945 consistent gets
0 physical reads
0 redo size
2075973 bytes sent via SQL*Net to client
14989 bytes received via SQL*Net from client
1322 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
19804 rows processed
10.2.0.5库中同名列增加表名前缀执行OK
1
SQL> set autot trace
SQL> set lines 100
SQL> SELECT yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx,
2 item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh,
3 TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb,zyh,ch,xm,
4 a.bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
5 sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy,
6 lb_yp_yl, zsq_code
7 FROM op.yz a LEFT OUTER JOIN op.pc b
8 ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), ' ')
9 LEFT JOIN op.zy p ON a.zyh = p.zyh
10 WHERE p.cy='在院' AND p.new_patient='1'
11 AND upper(nvl(p.bj,1))<> 'Y'
12 AND (state = '已核对')
13 AND is_in_bill IS NULL
14 ORDER BY ksrq, yz_id ;
20629 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3468887510
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 2580 | 2968 (2)| 00:00:36 |
| 1 | SORT ORDER BY | | 10 | 2580 | 2968 (2)| 00:00:36 |
|* 2 | HASH JOIN OUTER | | 10 | 2580 | 2967 (2)| 00:00:36 |
|* 3 | TABLE ACCESS BY INDEX ROWID| YZ | 3 | 672 | 42 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 10 | 2390 | 2963 (2)| 00:00:36 |
|* 5 | TABLE ACCESS FULL | ZY | 3 | 45 | 2917 (2)| 00:00:36 |
|* 6 | INDEX RANGE SCAN | DZBLYZ_ZYH | 118 | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | PC | 33 | 627 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NVL(TRIM(UPPER("A"."PC")),' ')=NVL(TRIM(UPPER("B"."PC"(+))),' '))
3 - filter("A"."STATE"='已核对' AND "A"."IS_IN_BILL" IS NULL)
5 - filter("P"."CY"='在院' AND UPPER(NVL("P"."BJ",'1'))<>'Y' AND
"P"."NEW_PATIENT"='1')
6 - access("A"."ZYH"="P"."ZYH")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
42121 consistent gets
0 physical reads
0 redo size
2181383 bytes sent via SQL*Net to client
15617 bytes received via SQL*Net from client
1377 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20629 rows processed
Bug 5368296: SQL NOT GENERATING ORA-918 WHEN USING JOIN
Bug 12388159 : SQL REPORTING ORA00918 AFTER UPGRADE TO 10.2.0.5.0
再次提醒：再小的系统数据库升级都需要做,功能测试,SPA测试,确保升级后功能和性能都正常.
