Find who is executing what command using DECODE

DECODE is a very powerful tool that can make SQL very efficient or very dense and nonintutive. Using the example below, you can easily track which user is doing what.

In this example, we query the V$SESSION table to see who is executing which command in the database. The COMMAND column displays a numberic code for each command, but we want to report a textual description for a few important commands. We use DECODE in the fourth column to examine the contents of V$SESSION.COMMAND. If the COMMAND is 0, than we display 'None', if it is 2, we display 'Insert', and so on. If the command is not in our list, we display the default, 'Other'. DECODE does not have to return a value; it can return NULL. This tip has been tested on Oracle versions 8, 8i and 9i; it probably works on Oracle version 7 as well.

SELECT sid, serial#, username, DECODE(command, 0, 'None', 2, 'Insert', 3, 'Select', 6, 'Update', 7, 'Delete', 8, 'Drop', 'Other') 
command from v$session where type <> 'BACKGROUND';

Reader Feedback

Stephan B. writes: I suggest one should rather use '!=' instead of '<>' (as recommended by Tom Kyte). Hence, the statement should look like this:

SELECT sid, serial#, username, DECODE(command, 0, 'None', 2, 'Insert', 3, 'Select', 6, 'Update', 7, 'Delete', 8, 'Drop', 'Other') command
from v$session where type != 'BACKGROUND';

I would even suggest to extend/replace the 'Other' clause by looking up the real 'other' activity. The query looks like this:

col command for a25 hea "Running"
SELECT s.sid, s.serial#, s.username,
       DECODE(s.command, 0, 'None',
                         2, 'Insert',
                         3, 'Select',
                         6, 'Update',
                         7, 'Delete',
                         8, 'Drop',
                         initcap(a.name)) command
from   v$session s,
       audit_actions a
where  s.type != 'BACKGROUND'
and    s.command = a.action;

Another alternative: In order to stay 'flexible for the future' (we do not know when and how the numbering of actions are extended by Oracle) I suggest that we use an outer join:

SELECT s.sid, s.serial#, s.username,
       DECODE(s.command, 0, 'None',
                         2, 'Insert',
                         3, 'Select',
                         6, 'Update',
                         7, 'Delete',
                         8, 'Drop',
                         nvl(initcap(a.name), 'Other('||s.command||')')) command
from   v$session s, audit_actions a
where  s.type != 'BACKGROUND'
and    s.command = a.action (+)
/

One could ask now why not directly use the query without a decode, such as:

SELECT s.sid, s.serial#, s.username,
       nvl(initcap(a.name), 'Other('||s.command||')') name
from   v$session s, audit_actions a
where  s.type != 'BACKGROUND'
and    s.command = a.action (+)
/

Rich B. writes: This can also be written using the CASE expression. I've found that CASE is somewhat easier to manage than DECODE, especially in nested or compound statements.

SELECT s.sid, s.serial#, s.username, 
       (case when s.command = 0 then 'None' 
             when s.command = 2 then 'Insert' 
             when s.command = 3 then 'Select' 
             when s.command = 6 then 'Update' 
             when s.command = 7 then 'Delete' 
             when s.command = 8 then 'Drop' 
               else 'Other('||s.command||')' 
       end) command 
from   v$session s, audit_actions a 
where  s.type != 'BACKGROUND' 
and    s.command = a.action (+) 

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.