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…

ORA-00028

This example shows how to kill a user session. You can view sessions in your database as follows:

SQL> col username format a10
SQL> l
  1  select username, sid, serial#, status
  2  from v$session
  3* where username = 'ANDREW'
SQL> /

USERNAME          SID    SERIAL# STATUS
---------- ---------- ---------- --------
ANDREW            143         79 INACTIVE

SQL>

You can then use the SID and SERIAL# displayed to kill a session as shown below. The username you are interested in may have more than one session. If this happens, you will need to use one or more of the other columns in V$SESSION (e.g.OSUSER, MACHINE, LOGON_TIME etc) to see exactly which session you want to get rid of:

SQL> alter system kill session '143,79';
 

System altered.
 

SQL>

The killed session stays in V$SESSION with a status of KILLED:

SQL> select username, sid, serial#, status
  2  from v$session
  3  where username = 'ANDREW';

USERNAME          SID    SERIAL# STATUS
---------- ---------- ---------- --------
ANDREW            143         79 KILLED

SQL>

Until the user tries to access it again (the connection was made earlier). He will then get an ORA-00028:

SQL> conn andrew/reid@test10
Connected.
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

SQL>

... and the session will disappear from V$SESSION:

SQL> select username, sid, serial#, status
  2  from v$session
  3  where username = 'ANDREW';

no rows selected

SQL>

ORA-00030

This was tested on Oracle 11.2. When you run ALTER SYSTEM KILL SESSION ‘X,Y’; X must be a SID from V$SESSION and Y must be the SERIAL# from the same row. If not, you will get an ORA-00030: 

SQL> select count(*) from v$session

  2  where sid = 123

  3  and serial# = 321

  4  /

 

  COUNT(*)

----------

         0

 

SQL> alter system kill session '123,321'

  2  /

alter system kill session '123,321'

*

ERROR at line 1:

ORA-00030: User session ID does not exist.

 

SQL>

ORA-00026

This was tested on Oracle 9. When you use the ALTER SYSTEM KILL SESSION command, you must supply the SID and SERIAL# from an entry in V$SESSION. You get an ORA-00026 if you do not provide one: 

SQL> alter system kill session

  2  /

alter system kill session

                        *

ERROR at line 1:

ORA-00026: missing or invalid session ID

 

SQL>

 

The SID and SERIAL# are both numbers so you also get an ORA-00026 if you give non numeric values:

 

SQL> alter system kill session 'A,B'

  2  /

alter system kill session 'A,B'

*

ERROR at line 1:

ORA-00026: missing or invalid session ID

 

SQL>

 

… or values which do not come from V$SESSION:

 

SQL> select count(*) from v$session

  2  where sid = 123

  3  and serial# = 456

  4  /

 

  COUNT(*)

----------

         0

 

SQL> alter system kill session '123,456'

  2  /

alter system kill session '123,456'

*

ERROR at line 1:

ORA-00026: missing or invalid session ID

 

SQL>

 

However, if the values are valid, the command should work:

 

SQL> select sid, serial# from v$session

  2  where username = 'ANDREW'

  3  /

 

       SID    SERIAL#

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

        13         34

 

SQL> alter system kill session '13,34'

  2  /

 

System altered.

 

SQL>

Granting Access to Your Details in V$SESSION

You can allow a user to see details of his own session in V$SESSION like this. First do the following as SYS:

SQL> conn / as sysdba

Connected.

SQL> create view session_log as

  2  select * from v$session

  3  where username = user;

View created.

SQL> create public synonym session_log

  2  for session_log;

Synonym created.

SQL> grant select on session_log to public;

Grant succeeded.

SQL>

Now, still as SYS, create a user to test this out: 

SQL> grant create session

  2  to andrew identified by reid;

 

Grant succeeded.

SQL>

Connect as the user, show that you can only see 1 row in session_log and note its SID and SERIAL#:

 

SQL> conn andrew/reid

Connected.

SQL> select sid, serial# from session_log;

 

       SID    SERIAL#

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

       589        141

 

SQL>

Leave that session logged in to SQL*Plus and reconnect as SYS. Confirm that the SID and SERIAL# for Andrew's session match those shown above:

SQL> conn / as sysdba

Connected.

SQL> select sid, serial# from v$session

  2  where username = 'ANDREW';

       SID    SERIAL#

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

       589        141

SQL>

ORA-00027

In the SQL*Plus session below, I connect as SYS to an Oracle 10 database. Then I show that there is only one user logged on as SYS i.e. me. I note the sid and serial# and try to use them to kill my current session. Oracle does not allow this and displays an ORA-00027:

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> select sid, serial# from v$session
  2  where username = 'SYS'
  3  /

       SID    SERIAL#
---------- ----------
       159          5

SQL> alter system kill session '159,5';
alter system kill session '159,5'
*
ERROR at line 1:
ORA-00027: cannot kill current session

SQL>