首页 > 代码库 > EBS_DBA_问题:关于ORA-01438: value larger than specified precision allowed for this column

EBS_DBA_问题:关于ORA-01438: value larger than specified precision allowed for this column

 

问题模拟:

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 testinsert into dh_t values (v_id,v_name);commit;end;/create or replace procedure p_dh2 as v_cnt number;begin----just for errorstack testselect 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 2014Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsSQL> set linesize 200 pagesize 999SQL> col tracefile format a100SQL> 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.trcSQL> 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 columnORA-06512: at "DBMON.P_DH1", line 6ORA-06512: at "DBMON.P_DH2", line 7ORA-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.trcOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/DEV/db/tech_st/11.2.0System name:    LinuxNode name:    erp03.lasland.comRelease:    2.6.39-300.26.1.el5uekVersion:    #1 SMP Thu Jan 3 18:31:38 PST 2013Machine:    x86_64Instance name: DEVRedo thread mounted by this instance: 1Oracle process number: 275Unix 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  name0x127f2f128         6  procedure SYS.P_DH10x103e87f48         7  procedure SYS.P_DH20x15bebdba8         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=0Starting SQL statement dumpSQL Informationuser_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  name0x127f2f128         6  procedure SYS.P_DH10x103e87f48         7  procedure SYS.P_DH20x15bebdba8         1  anonymous blocksql_text_length=36sql=INSERT INTO DH_T VALUES (:B2 ,:B1 )

再来看看一个cursor用了多少UGA

 

 

 

 

 

EBS_DBA_问题:关于ORA-01438: value larger than specified precision allowed for this column