oracle.-904,ORACLE 11G ORA-904 while running select query(BUG)

Bug 17551261 : ORA-904 WHILE RUNNING SELECT QUERY

做完从10g向11.2.0.4.6库的迁移之后,开启应用后有部分数据报ORA-904错，

排查同义词权限，访问权限，均没有问题。

SQL> select * from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

PL/SQL Release 11.2.0.4.0 – Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 – Production

NLSRTL Version 11.2.0.4.0 – Production

出现错误的语句:

SELECT

T1.ID,

T1.WAYBILL_NO,

T1.SENDER_NAME,

T1.SENDER_PHONE,

T1.SEND_STATION_ID,

T1.SEND_STATION_NAME,

T1.DESTINATION_ID,

T1.DESTINATION_NAME,

T1.GOODS_TYPE,

T1.GOODS_NAME,

T1.APPLY_TYPE,

T1.APPLYER_PHONE,

T1.APPLER_CODE,

T1.APPLYER_NAME,

T1.APPLYER_STATION_ID,

T1.APPLYER_STATION_NAME,

T1.APPLY_DATE,

T1.ARBITRAGE_DATE,

T1.STATE,

T1.ARBITRAGE_STATION_ID,

T1.ARBITRAGE_STATION_NAME,

T1.ARBITRAGE_USER_CODE,

T1.ARBITRAGE_USER_NAME,

T1.REJECT_FOREVER,

T1.CONFIRM_DATE,

T1.HISTORY_ID,

T1.SEND_DATE,

T1.WORTH,

T1.WEIGHT,

T1.AMOUNT,

T1.INQUIRY_PROCESS,

T1.RECEIVER_NAME,

T1.RECEIVER_PHONE,

T1.RECEIVER_COMPANY,

T1.RECEIVER_ADDRESS,

T1.SENDER_COMPANY,

T1.SENDER_ADDRESS,

T1.REMARK,

T1.IS_TAOBAO,

T1.IS_VALUABLES,

T1.IS_INSURANCE,

T1.VALUABLES_BILL_NUMBER,

T1.TAOBAO_BILL_NUMBER,

T1.INSURANCE_AMOUNT,

T1.ARBITRAGE_RESULT,

T1.PROCESS_USER_CODE,

T1.PROCESS_USER_NAME,

T1.ACCIDENTPARTS,

T1.ACCORDING_TYPE,

T1.REJECT_REASON,

T1.TRANSPORT_TYPE,

T1.IS_ARBITRAGE_USER_CODE,

T1.PROCESS_REMARK,

T1.ABOLISH_REASON,

T1.ABOLISH_NAME,

T1.ABOLISH_DATE,

T3.DEAL_DEPARTMENT_ID,

T5.STATE_NAME,

T3.NAME AS APPLY_TYPE_NAME,

T6.NAME AS GOODS_TYPE_NAME

FROM AO_PROBLEM_APPLY T1

INNER JOIN AO_DICT T3 ON T1.APPLY_TYPE=T3.CODE

LEFT JOIN AO_DICT T6 ON T1.GOODS_TYPE=T6.CODE

LEFT JOIN t_mdm_org T2 ON T2.CODE=T1.DESTINATION_ID

LEFT JOIN V_MDM_ORG_DISTRICT T4 ON T4.CODE = T2.CODE

LEFT JOIN AO_STATE T5 ON T5.ID=T1.STATE;

getting error ORA-904: “from$_subquery$_003″.”SK_COD_FILIAL” while running following SQL

alert日志没有ORA-904错误，没有相关trac信息

apply path后，解决此问题

[oracle@webbase01 OPatch]$ ./opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.4

Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/dbhome_1/oraInst.loc

OPatch version : 11.2.0.3.4

OUI version : 11.2.0.4.0

Log file location : /u01/app/oracle/dbhome_1/cfgtoollogs/opatch/opatch2015-09-06_14-37-14PM_1.log

Lsinventory Output file location : /u01/app/oracle/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-09-06_14-37-14PM.txt

——————————————————————————–

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0

There are 1 products installed in this Oracle Home.

Interim patches (2) :

Patch 17551261 : applied on Tue Sep 01 21:50:17 CST 2015======================》解决ORA-904的bug问题

Unique Patch ID: 17274505

Created on 9 Feb 2014, 12:44:44 hrs PST8PDT

Bugs fixed:

17551261

Patch 20299013 : applied on Mon Jun 15 15:05:37 CST 2015

Unique Patch ID: 18573940

Patch description: “Database Patch Set Update : 11.2.0.4.6 (20299013)”

Created on 4 Mar 2015, 02:27:44 hrs PST8PDT

Sub-patch 19769489; “Database Patch Set Update : 11.2.0.4.5 (19769489)”

Sub-patch 19121551; “Database Patch Set Update : 11.2.0.4.4 (19121551)”

Sub-patch 18522509; “Database Patch Set Update : 11.2.0.4.3 (18522509)”