Friday, June 13, 2014

DBMS_MONITOR.SESSION_TRACE_ENABLE

This happened on Oracle 11.2. I had a problem with a 3rd 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…

No comments: