问题模拟:
1、创建表和PL/SQL函数 create table dh_t (id number(2,1),name varchar2(200)); create or replace procedure p_dh1 as v_id number :=1234335; v_name varchar2(200) :='oradh'; begin --just for errorstack test insert into dh_t values (v_id,v_name); commit; end; / create or replace procedure p_dh2 as v_cnt number; begin ----just for errorstack test select count(*) into v_cnt from dh_t; dbms_output.put_line('the dh_t count is '||v_cnt); p_dh1; end; / 2、进行errorstack跟踪 SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 1 11:15:52 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> set linesize 200 pagesize 999 SQL> col tracefile format a100 SQL> select spid,tracefile from v$process a where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1)); SPID TRACEFILE --------------- -------------------------------------------------------------------------------------------------- 32882 /u01/DEV/db/tech_st/11.2.0/admin/DEV_erp03/diag/rdbms/dev/DEV/trace/DEV_ora_32882.trc SQL> alter session set events='1438 trace name errorstack forever,level 3'; Session altered. SQL> exec p_dh2; BEGIN p_dh2; END; * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column ORA-06512: at "DBMON.P_DH1", line 6 ORA-06512: at "DBMON.P_DH2", line 7 ORA-06512: at line 1 [oracle@192oracle ~]$ ls -ltr /u01/DEV/db/tech_st/11.2.0/admin/DEV_erp03/diag/rdbms/dev/DEV/trace/DEV_ora_32882.trc
--下载/u01/DEV/db/tech_st/11.2.0/admin/DEV_erp03/diag/rdbms/dev/DEV/trace/DEV_ora_32882.trc
打开:
Trace file /u01/DEV/db/tech_st/11.2.0/admin/DEV_erp03/diag/rdbms/dev/DEV/trace/DEV_ora_32882.trc Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/DEV/db/tech_st/11.2.0 System name: Linux Node name: erp03.lasland.com Release: 2.6.39-300.26.1.el5uek Version: #1 SMP Thu Jan 3 18:31:38 PST 2013 Machine: x86_64 Instance name: DEV Redo thread mounted by this instance: 1 Oracle process number: 275 Unix process pid: 32882, image: oracle@erp03.lasland.com (TNS V1-V3) *** 2016-08-31 14:22:47.187 *** SESSION ID:(2947.4137) 2016-08-31 14:22:47.187 *** CLIENT ID:() 2016-08-31 14:22:47.187 *** SERVICE NAME:(SYS$USERS) 2016-08-31 14:22:47.187 *** MODULE NAME:(sqlplus@erp03.lasland.com (TNS V1-V3)) 2016-08-31 14:22:47.187 *** ACTION NAME:() 2016-08-31 14:22:47.187 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0) ----- Error Stack Dump ----- ORA-01438: value larger than specified precision allowed for this column ----- Current SQL Statement for this session (sql_id=b8n03s73k7d39) ----- INSERT INTO DH_T VALUES (:B2 ,:B1 ) ----- PL/SQL Stack ----- ----- PL/SQL Call Stack ----- object line object handle number name 0x127f2f128 6 procedure SYS.P_DH1 0x103e87f48 7 procedure SYS.P_DH2 0x15bebdba8 1 anonymous block
可以看到执行情况 是先执行1再执行procedure SYS.P_DH2的第七行 然后执行procedure SYS.P_DH1的第六行,发生了问题.
然后结合INSERT INTO DH_T VALUES (:B2 ,:B1 ) 继续深入看看什么情况导致的.
查看相关代码\
select line, text from dba_source where owner = 'SYS' and name = 'P_DH1' order by line asc;
查看第六行:
insert into dh_t values (v_id,v_name);
查看一下他的变量是什么:
打开跟踪文件，通常第一步做的是搜索第一个"Session Cursor Dump",当搜索它的时候，将看到如下的输出:
----- Session Cursor Dump -----
Current cursor: 3, pgadep=1
搜索Cursor#3
Cursor#3(0x7feee6691d50) state=BOUND curiob=0x7feee657b2e0
curflg=cd fl2=0 par=(nil) ses=0x1559b0c00
----- Dump Cursor sql_id=b8n03s73k7d39 xsc=0x7feee657b2e0 cur=0x7feee6691d50 -----
往下拉 看到相关的情况:
----- Bind Info (kkscoacd) ----- Bind#0 oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00 oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7feee66ce2f0 bln=22 avl=05 flg=09 value=1234335 Bind#1 oacdty=01 mxl=2000(200) mxlc=00 mal=00 scl=00 pre=00 oacflg=13 fl2=206001 frm=01 csi=871 siz=2000 off=0 kxsbbbfp=7feee66ce330 bln=2000 avl=05 flg=09 value="oradh" Frames pfr 0x7feee657b258 siz=3472 efr 0x7feee657b178 siz=3416 Cursor frame dump enxt: 3.0x00000550 enxt: 2.0x00000040 enxt: 1.0x000007c8 pnxt: 1.0x00000038 kxscphp=0x7feee651d4e8 siz=984 inu=584 nps=360 kxscbhp=0x7feee651d608 siz=984 inu=152 nps=0 kxscwhp=0x7feee64fd608 siz=4056 inu=56 nps=0 Starting SQL statement dump SQL Information user_id=0 user_name=SYS module=sqlplus@erp03.lasland.com (TNS V1-V3) action= sql_id=b8n03s73k7d39 plan_hash_value=0 problem_type=0 ----- Current SQL Statement for this session (sql_id=b8n03s73k7d39) ----- INSERT INTO DH_T VALUES (:B2 ,:B1 ) ----- PL/SQL Stack ----- ----- PL/SQL Call Stack ----- object line object handle number name 0x127f2f128 6 procedure SYS.P_DH1 0x103e87f48 7 procedure SYS.P_DH2 0x15bebdba8 1 anonymous block sql_text_length=36 sql=INSERT INTO DH_T VALUES (:B2 ,:B1 )
再来看看一个cursor用了多少UGA