DBMS_MONITOR.SESSION_TRACE_ENABLE

This happened on Oracle 11.2. I had a problem with a 3^rd party application hosted in the UK with users in another country. The front-end application was failing regularly with ORA-01426. I needed to know which SQL was causing these errors. First I logged into a test database as user ORACLE:

 

SQL> show user

USER is "ORACLE"

SQL>

 

Then I started a trace of this session as follows:

 

SQL> show user

USER is "SYS"

SQL> select sid, serial# from v$session

  2  where username = 'ORACLE'

  3  /

 

       SID    SERIAL#

---------- ----------

       102       1354

 

SQL> exec dbms_monitor.session_trace_enable(102,1354);

 

PL/SQL procedure successfully completed.

 

SQL>

 

I ran some SQL to produce an ORA-01426 in the traced session:

 

SQL> show user

USER is "ORACLE"

SQL> select power(70,70) from dual

  2  /

select power(70,70) from dual

       *

ERROR at line 1:

ORA-01426: numeric overflow

 

SQL>

 

Then I stopped the trace:

 

SQL> show user

USER is "SYS"

SQL> exec dbms_monitor.session_trace_disable(102,1354);

 

PL/SQL procedure successfully completed.

 

SQL>

 

… but when I looked for the statement in the trace file, I could see no mention of the ORA-01426:

 

=====================

PARSING IN CURSOR #2 len=29 dep=0 uid=8354 oct=3 lid=8354 tim=31182397151149 hv=1978710958 ad='397d685b0' sqlid='0c4sm5duz1fxf'

select power(70,70) from dual

END OF STMT

PARSE #2:c=0,e=217,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1546270724,tim=31182397151146

EXEC #2:c=0,e=403,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1546270724,tim=31182397532682

WAIT #2: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=31182397532829

FETCH #2:c=0,e=199,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1546270724,tim=31182397533145

STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'

=====================

 

I had hoped that I would be able to trace the application, search the trace file(s) for the ORA-01426 and thus identify the failing SQL but it looks as if I will need to try something else. More to follow…