Hi all, Thanks for the feedback so far, and.... Oracle 9.2.0.5 on Red Hat Enterprise Linux ES release 3.. sorry forgot about that :-(
RE sql_trace: yes, its was only set for a minute or two.
RE alter system unset events '1401 trace name errorstack level 10';
I'm having a play with this, but can't produce the trace file I was expecting:
As app user: -- ---- ---- ---- -- SQL> create table xxx (a number(2), b varchar2(5));
As system: -- ---- ---- ---- -- SQL> alter system set events '1401 trace name errorstack level 10';
As app user: -- ---- ---- ---- -- SQL> insert into xxx values (12, 'abcdef'); ORA-01401 (See ORA-01401.ora-code.com): inserted value too large for column
I don't get any trace file or even an entry in the alert log.
I also tried (as system) alter system set events '10046 trace name context forever, level 12'; Here I got a lot of trace file useful for tuning, ie tracing waits etc. But still no dumpp of the offending sql that caused the ORA-01401 (See ORA-01401.ora-code.com).
To Tony.Adolph@(protected) cc oracle-l@(protected), Jonas.Kilian@(protected) Subject Re: ORA-01401 (See ORA-01401.ora-code.com): inserted value too large for column
Comments embedded
On Thu, 24 Feb 2005 14:30:18 +0100, Tony.Adolph@(protected) <Tony.Adolph@(protected)> wrote: > Hi all, > A developer here is trying to find the source of the error "ORA-01401 (See ORA-01401.ora-code.com): > inserted value too large for column". He want to know which table / > column(s) are causing the problem. > > The error is not logged in the alert log.
That would be normal, it should be thrown back to the offending application. All this rather suggests that the app could do with rather better exception handling, c'est la vie.
> I set sql_trace = true and had him repeate his tests. Although we got > bucket loads of trace files in both user_dump_dest and > backfround_dump_dest we did not get any trace files or errors relating to > ORA-01401 (See ORA-01401.ora-code.com).
If you set this at the instance level - which it rather sounds like you did - I suggest you unset it before you fill the disk!
> I tried to grep for just the 1401 string, still nothing. > > How can find the culprit here folks?
A number of ways.
You can set a 1401 event in the init.ora/spfile You can persuade the developer to set event 10046 at logon. You could even persuade them to use DBMS_APPLICATION_INFO You can use an after servererror trigger like the one I posted for the temp tablespace error a couple of days ago.
