模拟跟踪错误

  1. -- 创建测试表
  2. SQL> create table tab1 (id number(2,1));
  4. Table created.
  6. SQL> var n number
  8. SQL> exec :n := 100.2
  10. PL/SQL procedure successfully completed.
  12. -- 模拟错误
  13. SQL> insert into tab1 values(:n);
  14. insert into tab1 values(:n)
  15.                          *
  16. ERROR at line 1:
  17. ORA-01438: value larger than specified precision allowed for this column
  19. -- 实例级别 监控1438错误
  20. SQL> alter system set events '1438 trace name errorstack forever,level 3';
  22. System altered.
  24. -- 会话级别 监控1438错误
  25. -- alter session set events='1438 trace name errorstack forever,level 3';
  27. SQL> insert into tab1 values(:n);
  28. insert into tab1 values(:n)
  29.                          *
  30. ERROR at line 1:
  31. ORA-01438: value larger than specified precision allowed for this column
  33. -- 跟踪到以后，记得关闭trace，否则可能生成大量日志，撑爆空间
  34. SQL> alter system set events '1438 trace name errorstack off';
  36. System altered.

trace文件通用解读办法

也适用于系统dump出来的trace文件等等

  3. vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9070.trc
  5. *** 2021-04-14 15:10:58.345
  6. *** SESSION ID:(125.397) 2021-04-14 15:10:58.345
  7. *** CLIENT ID:() 2021-04-14 15:10:58.345
  8. *** SERVICE NAME:(SYS$USERS) 2021-04-14 15:10:58.345
  9. *** MODULE NAME:(sqlplus@orcl (TNS V1-V3)) 2021-04-14 15:10:58.345
  10. *** ACTION NAME:() 2021-04-14 15:10:58.345
  12. dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
  13. ----- Error Stack Dump -----
  14. ORA-01438: value larger than specified precision allowed for this column
  16. # 执行的sql
  17. ----- Current SQL Statement for this session (sql_id=cby8j6hhr5mky) -----
  18. insert into tab1 values(:n)
  20. # 堆栈信息：
  21. ----- Call Stack Trace -----
  22. calling              call     entry                argument values in hex
  23. location             type     point                (? means dubious value)
  24. -------------------- -------- -------------------- ----------------------------
  26. *** 2021-04-14 15:10:58.860
  27. skdstdst()+41        call     kgdsdst()            000000000 ? 000000000 ?
  28.                                                    7FFC7ABE0C90 ? 7FFC7ABE0D68 ?
  29.                                                    7FFC7ABE5810 ? 000000002 ?
  30. ksedst1()+103        call     skdstdst()           000000000 ? 000000000 ?
  31.                                                    7FFC7ABE0C90 ? 7FFC7ABE0D68 ?
  32.                                                    7FFC7ABE5810 ? 000000002 ?
  33. ksedst()+39          call     ksedst1()            000000000 ? 000000001 ?
  34.                                                    7FFC7ABE0C90 ? 7FFC7ABE0D68 ?
  35.                                                    7FFC7ABE5810 ? 000000002 ?
  36. dbkedDefDump()+2746  call     ksedst()             000000000 ? 000000001 ?
  37.                                                    7FFC7ABE0C90 ? 7FFC7ABE0D68 ?
  38.                                                    7FFC7ABE5810 ? 000000002 ?
  39. ksedmp()+41          call     dbkedDefDump()       000000003 ? 000000000 ?
  40.                                                    7FFC7ABE0C90 ? 7FFC7ABE0D68 ?
  41.                                                    7FFC7ABE5810 ? 000000002 ?
  42. dbkdaKsdActDriver()  call     ksedmp()             000000003 ? 000000000 ?
  43. +1960                                              7FFC7ABE0C90 ? 7FFC7ABE0D68 ?
  44.                                                    7FFC7ABE5810 ? 000000002 ?
  45. dbgdaExecuteAction(  call     dbkdaKsdActDriver()  7FEAE431C730 ? 7FFC7ABE7F10 ?
  46. )+1065                                             7FFC7ABE0C90 ? 7FFC7ABE0D68 ?
  47.                                                    7FFC7ABE5810 ? 000000002 ?
  48. dbgdaRunAction()+81  call     dbgdaExecuteAction(  7FEAE431C730 ? 00A6006E0 ?
  49. 5                             )                    0020C0003 ? 7FFC7ABE7F10 ?
  50.                                                    000000001 ? 000000002 ?                                                                                                       
  52. # 绑定变量信息
  54. ----- Bind Info (kkscoacd) -----
  55.  Bind#0
  56.   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  57.   oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
  58.   kxsbbbfp=7feae3c68cb8  bln=22  avl=04  flg=05
  59.   value=100.2
  60.  Frames pfr 0x7feae3d7e108 siz=3152 efr 0x7feae3d7e040 siz=3096
  61.  Cursor frame dump                                                                         
  63. # 执行计划
  64. ============
  65. Plan Table
  66. ============
  67. --------------------------------------------+-----------------------------------+
  68. | Id  | Operation                 | Name    | Rows  | Bytes | Cost  | Time      |
  69. --------------------------------------------+-----------------------------------+
  70. | 0   | INSERT STATEMENT          |         |       |       |     1 |           |
  71. | 1   |  LOAD TABLE CONVENTIONAL  |         |       |       |       |           |
  72. --------------------------------------------+-----------------------------------+    
  75. # 等待事件和历史等待事件
  77.     Current Wait Stack:
  78.       Not in wait; last wait ended 2.081502 sec ago
  79.     Wait State:
  80.       fixed_waits=0 flags=0x21 boundary=(nil)/-1
  81.     Session Wait History:
  82.         elapsed time of 2.087036 sec since last wait
  83.      0: waited for 'SQL*Net message from client'
  84.         driver id=0x62657100, #bytes=0x1, =0x0
  85.         wait_id=40 seq_num=41 snap_id=1
  86.         wait times: snap=3.795465 sec, exc=3.795465 sec, total=3.795465 sec
  87.         wait times: max=infinite
  88.         wait counts: calls=0 os=0
  89.         occurred after 0.000031 sec of elapsed time
  90.      1: waited for 'SQL*Net message to client'
  91.         driver id=0x62657100, #bytes=0x1, =0x0
  92.         wait_id=39 seq_num=40 snap_id=1
  93.         wait times: snap=0.000001 sec, exc=0.000001 sec, total=0.000001 sec
  94.         wait times: max=infinite
  95.         wait counts: calls=0 os=0
  96.         occurred after 0.000004 sec of elapsed time
  97.      2: waited for 'SQL*Net break/reset to client'