应正确使用别名 避免ora-904 !

朋友反馈 一个SQL执行存在904问题 ，业务保密性质以下用模拟说明

正常语句 select * from dept  where dept.deptno=10

异常语句select * from dept  t where dept.deptno=10   声明别名但未使用  报ora-904

做10046 对应如下事件

WAIT #598250400: nam='SQL*Net message from client' ela= 3926807 driver id=1111838976 #bytes=1 p3=0 obj#=14232 tim=5011331461825
CLOSE #598250400:c=0,e=13,dep=0,type=1,tim=5011331462013
=====================
PARSE ERROR #598249136:len=41 dep=0 uid=99 oct=3 lid=99 tim=5011331462579 err=904
select * from dept t where dept.detpno=10
WAIT #598249136: nam='SQL*Net break/reset to client' ela= 3 driver id=1111838976 break?=1 p3=0 obj#=14232 tim=5011331462733
WAIT #598249136: nam='SQL*Net break/reset to client' ela= 106 driver id=1111838976 break?=0 p3=0 obj#=14232 tim=5011331462869
WAIT #598249136: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=14232 tim=5011331462907
 

 

语法规定，在语句解析的流程中就会返回。

一般在项目中确定规范，都使用表别名

10053分析 SQL在解析中 的确如此

sql=select * from dept t where t.deptno=10
----- Explain Plan Dump -----
----- Plan Table -----
 
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |     3 |           |
| 1   |  TABLE ACCESS FULL | DEPT    |     1 |    20 |     3 |  00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("T"."DEPTNO"=10)
 
Content of other_xml column
===========================
  db_version     : 11.2.0.4
  parse_schema   : AMBASE
  plan_hash      : 3383998547
  plan_hash_2    : 237223545
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
    END_OUTLINE_DATA
  */