主页 / dba / 问题 / 278140
Accepted
Mikhail Aksenov
Asked: 2020-10-16 07:43:19 +0800 CST

如何在跟踪文件中获取语法和逻辑 ORA？

  • 772

有一个包含大量查询的应用程序，其中一个导致 ORA-1722

检查：我打开会话跟踪，如：

alter session set events '10046 trace name context forever, level 12'

并对数据库说

SELECT TO_NUMBER('asdf') FROM DUAL;

我实际上在客户端收到 ORA-1722，但看不到该查询以错误代码完成：

PARSING IN CURSOR #140193170131176 len=34 dep=0 uid=9 oct=3 lid=9 tim=24633865068083 hv=3356444932 ad='d25d6b00' sqlid='cmpv4d740yk84'
SELECT TO_NUMBER('asdf') FROM DUAL
END OF STMT
PARSE #140193170131176:c=2315,e=2207,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=24633865068082
EXEC #140193170131176:c=27,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=24633865068204
WAIT #140193170131176: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=24633865068275
FETCH #140193170131176:c=19,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=24633865068331
STAT #140193170131176 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 str=1 time=1 us cost=2 size=0 card=1)'
WAIT #140193170131176: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=24633865068482
WAIT #140193170131176: nam='SQL*Net break/reset to client' ela= 27988 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=24633865096496
WAIT #140193170131176: nam='SQL*Net message from client' ela= 537077 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=24633865633643
CLOSE #140193170131176:c=8,e=9,dep=0,type=0,tim=24633865633723
PARSE #140193170129200:c=32,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=24633865633836
BINDS #140193170129200:

 Bind#0
  oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1400010 frm=01 csi=873 siz=32767 off=0
  kxsbbbfp=7f8143f31000  bln=32767  avl=00  flg=05
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1400000 frm=01 csi=873 siz=24 off=0
  kxsbbbfp=7f814424b060  bln=22  avl=00  flg=05
WAIT #140193170129200: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=24633865634181
EXEC #140193170129200:c=336,e=336,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=24633865634210
XCTEND rlbk=0, rd_only=1, tim=24633865634250

如何检测导致 ORA-1722（或任何其他逻辑/语法问题）的查询？

oracle trace

1 个回答

  1. Best Answer

    您可以跟踪特定错误，如下所示：

    SQL> alter session set events '1722 trace name errorstack level 1';

Session altered.

SQL> SELECT TO_NUMBER('asdf') from dual;
SELECT TO_NUMBER('asdf') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

    在跟踪文件中：

    *** 2020-10-15T18:56:23.811818+02:00
*** SESSION ID:(18.12475) 2020-10-15T18:56:23.811837+02:00
*** CLIENT ID:() 2020-10-15T18:56:23.811841+02:00
*** SERVICE NAME:(SYS$USERS) 2020-10-15T18:56:23.811845+02:00
*** MODULE NAME:(SQL*Plus) 2020-10-15T18:56:23.811849+02:00
*** ACTION NAME:() 2020-10-15T18:56:23.811853+02:00
*** CLIENT DRIVER:(SQL*PLUS) 2020-10-15T18:56:23.811856+02:00

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
<error barrier> at 0x7ffdfe31ce40 placed dbkda.c@296
ORA-01722: invalid number
----- Current SQL Statement for this session (sql_id=bkusfcu38gbjr) -----
SELECT TO_NUMBER('asdf') from dual

    要记录它们，您可以使用 servererror 触发器，如下所示：

    在 Oracle 中是否可以跟踪导致错误的 SQL 语句？

    • 1

相关问题