首页 > 代码库 > 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