12c 语法错误的sql日志记录

当sql语句不小心写错的时候，通常会报ORA-00923的错误

SYS@xb> select count(1) frm user_tables;
select count(1) frm user_tables
                    *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

但是很遗憾的是这个错误并不会在alert日志里产生，试想一下如果这些连接是通过应用端发起的，大量的语法错误的语句会进行sql解析等操作，无疑会消耗系统资源，而且你还无法知晓。

当然你也许可以通过设置10035事件来进行分析

11.2.0.4

SYS@xb> oradebug setmypid
Statement processed.
SYS@xb> oradebug event 10035  trace name context forever,level 1;
Statement processed.

SYS@xb> 
declare
        n number;
begin
        for i in 1..200 loop
        begin
                execute immediate 'select count(1) frm user_tables' into n;
        exception
                when others then null;
        end;
        end loop;
end;
 12  /

PL/SQL procedure successfully completed.

现在观察下alert日志

PARSE ERROR: ospid=7940, error=923 for statement:
select count(1) frm user_tables^@
Additional information: hd=0x9239a950 phd=0x9239add0 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
----- pl/sql Call Stack -----
  object      line  object
  handle    number  name
0x92342260         6  anonymous block
PARSE ERROR: ospid=7940, error=923 for statement:
select count(1) frm user_tables^@
Additional information: hd=0x9239a950 phd=0x9239add0 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
----- pl/sql Call Stack -----
  object      line  object
  handle    number  name
0x92342260         6  anonymous block

可以看到alert日志里也记录了详细的错误信息,包括error=923，能很轻易的发现到错误的sql，也可以根据给出的地址去数据库里查询

SYS@xb> select sql_text from v$sql where child_address='0000000092342260';

SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
declare         n number; begin         for i in 1..200 loop         begin                 execute immediate 'select count(1) frm user_tables' into n;         exception                 when others then null;         end;         end loop; end;

但是生产环境中肯定不能这样直接设置事件的方式，否则会产生大量的trace

好消息是从12.2.0.1开始，oracle会自动对这类错误进行日志写入

SYS@ora12c> 
declare
        n number;
begin
        for i in 1..200 loop
        begin
                execute immediate 'select count(1) frm user_tables' into n;
        exception
                when others then null;
        end;
        end loop;
end;
 12  /

PL/SQL procedure successfully completed.


WARNING: too many parse errors, count=400 SQL hash=0x7ad2b6ab
PARSE ERROR: ospid=31909, error=923 for statement:
Wed Nov 28 17:15:17 2018
select count(1) frm user_tables
Additional information: hd=0x7537b1c0 phd=0x756990e8 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
Wed Nov 28 17:15:17 2018
----- pl/sql Call Stack -----
  object      line  object
  handle    number  name
0x75793c90         6  anonymous block

很方便的记录在alert日志里，意思表示警告，有太多的sql解析错误了，错误代码为923。

当然你如果实在不喜欢这个新特性，也可以通过打19691800补丁屏蔽掉