Question :
I am trying to generate a trace file when arise errors with code ORA-01461.
I have executed the following statements:
alter system set events '1461 trace name context forever, level 4';
from alert.log:
OS Pid: 4069 executed alter system set events '1461 trace name context forever, level 4'
from oradebug:
oradebug setmypid
oradebug eventdump system
1461 trace name CONTEXT level 4, forever
so, the event 1461 seems to be enabled. But if I try to arise an error ORA-01461 by hand, for example:
CREATE TABLE test (value VARCHAR2(1300 CHAR));
DECLARE
value VARCHAR2(5000 CHAR);
begin
value := RPAD('v', 4000, 'v') || 'test';
insert into test values(value);
END;
/
ORA-01461: can bind a LONG value only for insert into a LONG column
, no one tracefile is generated nor any line is added to the alert.log.
Database version:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit
Production
Thank you in advance
Edit:
@Balazs Papp:
Thank you, setting event 1461 like you suggest:
alter system set events '1461 trace name errorstack level 3';
allows Oracle to generate a .trc file.
This is a statement that arises the error (an update):
UPDATE RAE_TASKS SET RAET_DESTINATION = :v0 , RAET_OWNER = :v1 , RAET_NEXT_RUN = :v2 , RAET_APP_VER = :v3 , RAET_CUSTOMER_ID = :v4 , RAET_START_OF_PERIOD = :v5 ,
RAET_FAILURES = :v6 , RAET_DURATION = :v7 , RAET_SOURCE = :v8 , RAET_CONTEXT = :v9 , RAET_STATUS = :v10 , RAET_ADD_INFO_2 = :v11 , RAET_TYPE = :v12 , RAET_GRANUL
ARITY = :v13 WHERE RAET_ID = :v14
In order to examine the error a wanna check the bind variable passed.
But in the bind variables section is showed only the value of the first bind variable “Bind#0.. value=”etl””.. and also the siz is only showed for the first variable.
Just a snipped in the following
Bind#0
oacdty=96 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=871 siz=392 off=0
kxsbbbfp=ffffffff7b76e8d0 bln=32 avl=03 flg=05
value="etl"
Bind#1
oacdty=96 mxl=32(58) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=871 siz=0 off=32
kxsbbbfp=ffffffff7b76e8f0 bln=32 avl=00 flg=01
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=64
kxsbbbfp=ffffffff7b76e910 bln=22 avl=00 flg=01
Bind#3
oacdty=96 mxl=32(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=871 siz=0 off=88
kxsbbbfp=ffffffff7b76e928 bln=32 avl=00 flg=01
Bind#4
oacdty=101 mxl=08(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=120
kxsbbbfp=ffffffff7b76e948 bln=08 avl=00 flg=01
...
...
Why Oracle does not show all values/sizes?
Edit2:
@Balazs Papp: I add the bit that remains:
Bind#5
oacdty=02 mxl=22(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=128
kxsbbbfp=ffffffff7b76e950 bln=22 avl=00 flg=01
Bind#6
oacdty=02 mxl=22(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=152
kxsbbbfp=ffffffff7b76e968 bln=22 avl=00 flg=01
Bind#7
oacdty=101 mxl=08(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=176
kxsbbbfp=ffffffff7b76e980 bln=08 avl=00 flg=01
Bind#8
oacdty=96 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=871 siz=0 off=184
kxsbbbfp=ffffffff7b76e988 bln=32 avl=00 flg=01
Bind#9
oacdty=96 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=871 siz=0 off=216
kxsbbbfp=ffffffff7b76e9a8 bln=32 avl=00 flg=01
Bind#10
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=248
kxsbbbfp=ffffffff7b76e9c8 bln=22 avl=00 flg=01
Bind#11
oacdty=96 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=871 siz=0 off=272
kxsbbbfp=ffffffff7b76e9e0 bln=32 avl=00 flg=01
Bind#12
oacdty=96 mxl=32(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=871 siz=0 off=304
kxsbbbfp=ffffffff7b76ea00 bln=32 avl=00 flg=01
Bind#13
oacdty=96 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=871 siz=0 off=336
kxsbbbfp=ffffffff7b76ea20 bln=32 avl=00 flg=01
Bind#14
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=368
kxsbbbfp=ffffffff7b76ea40 bln=22 avl=00 flg=01
Frames pfr ffffffff7b76edd0 siz=10848 efr ffffffff7b76ece8 siz=10816
Cursor frame dump
enxt: 5.0x000004a8 enxt: 4.0x000007c0 enxt: 3.0x00000c78 enxt: 2.0x000001e0
enxt: 1.0x00000f80
pnxt: 2.0x00000008 pnxt: 1.0x00000018
kxscphp ffffffff7b740658 siz=1992 inu=736 nps=464
kxscbhp ffffffff7b740388 siz=984 inu=984 nps=416
The problem is that there are only these types: 96 (CHAR/NCHAR), 02 (NUMBER), 101 (BINARY_DOUBLE).
The desc of the affected table is as follows:
Name Null? Type
----------------------------------------- -------- ----------------------------
RAE_ID NOT NULL NUMBER(13)
RAE_CUSTOMER_ID NUMBER(4)
RAE_SOURCE VARCHAR2(250)
RAE_DESTINATION VARCHAR2(250)
RAE_START_OF_PERIOD NUMBER(13)
RAE_GRANULARITY VARCHAR2(50)
RAE_DURATION NUMBER(13)
RAE_NEXT_RUN NUMBER(13)
RAE_STATUS NUMBER(1)
RAE_TYPE VARCHAR2(30)
RAE_OWNER VARCHAR2(250)
RAE_APP_VER VARCHAR2(50)
RAE_CHUNK_DIMENSION VARCHAR2(250)
RAE_CHUNK_FROM NUMBER(13)
RAE_CHUNK_TO NUMBER(13)
RAE_FAILURES NUMBER(6)
RAE_END_OF_TASK NUMBER(13)
RAE_CONTEXT VARCHAR2(50)
RAE_ADD_INFO VARCHAR2(250)
RAE_ADD_INFO_2 VARCHAR2(250)
RAE_ADD_INFO_3 VARCHAR2(250)
RAE_ADD_INFO_4 VARCHAR2(250)
RAE_ADD_INFO_5 VARCHAR2(250)
RAE_ADD_INFO_6 VARCHAR2(250)
Answer :
Above setting doesn’t necessarily mean tracing, sometimes just enabling/disabling features if that is implemented. As far as I know, 1461 is not a diagnostic event, they are mostly in the 10000-11000 or higher regions, so I wouldn’t expect anything from the above. If you want trace files to be created on specific errors, enable the ERRORSTACK trace:
alter system set events '1461 trace name errorstack level 3';
Why exactly do you want to track down errors regarding ORA-01461? ORA-01461 is known to be related to various bugs, So depending on the problem you’re facing, I would first raise a SR with Oracle.
If you have MOSC access, take a look at Doc ID 456462.1.