0

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)
  • 1
    Can you post the full Bind# section? Even if the values are not shown, oacdty should reveal which bind variable has a LONG type and that should give a clue where to search further. Here is the reference for the codes: docs.oracle.com/cd/B19306_01/appdev.102/b14250/… – Balazs Papp Jul 22 '15 at 17:01
  • I have edited the post. The problem here is that in the trace does not appear oacdty=08 (08 is the code for LONG datatypes) – Shaunyl Jul 23 '15 at 8:05
2

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';

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.