Donald K. Burleson Oracle Tips

Show Oracle process detail

A little-known feature of the v$session view is the ability to decode the actual command that is being executed by an individual session.  The command column is coded in a special column called the v$session.command.  The following simple script can illustrate this column.

As we select from the v$session view we decode the command according to the numeric internal representation of each command.  This will allow us to create a data dictionary query that shows us each individual user, the program their executing, and the current command within that program that is being executed in their system.

This is extremely valuable for Oracle administrators who need to very quickly find out what is going on within their Oracle system.

select

   substr(s.username,1,18) username,

   substr(s.program,1,15) program,

   decode(s.command,

       0,'No Command',

       1,'Create Table',

       2,'Insert',

       3,'Select',

       6,'Update',

       7,'Delete',

       9,'Create Index',

      15,'Alter Table',

      21,'Create View',

      23,'Validate Index',

      35,'Alter Database',

      39,'Create Tablespace',

      41,'Drop Tablespace',

      40,'Alter Tablespace',

      53,'Drop User',

      62,'Analyze Table',

      63,'Analyze Index',

         s.command||': Other') command

from 

   v$session     s,

   v$process     p,

   v$transaction t,

   v$rollstat    r,

   v$rollname    n

where s.paddr = p.addr

and   s.taddr = t.addr (+)

and   t.xidusn = r.usn (+)

and   r.usn = n.usn (+)

order by 1

;

Here is a sample of the output, showing the individual command for each session:

USERNAME           PROGRAM         COMMAND          

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

APPS               f45runm@corp-hp Select

APPS               S:\ORANT\BIN\F5 Insert

APPS               S:\ORANT\BIN\R3 No Command

APPS               f45runm@corp-hp Select

APPS               S:\ORANT\BIN\R3 Select

APPS               S:\ORANT\BIN\R3 No Command

APPS               f45runm@corp-hp Update

APPS               S:\ORANT\BIN\R3 No Command

MWCEDI             TOAD.EXE        No Command

PERFSTAT           sqlplus.exe     Select

PERFSTAT           sqlplus.exe     No Command

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.