REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	c_user_ses_2.sql
REM
REM <SQLDIR_GRP>USER MAINT</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Displays all sessions DECODE command types
REM	(based on one of scripts from the oracle newsgroups)
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	c_user_ses_2.sql
REM 
REM Example:
REM	c_user_ses_2.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set pages 1000
set lines 180
set trims on
col username  format a10 trunc
col osuser    format a12 trunc
col command   format a10 trunc
col machine   format a15 trunc
col lockwait  format a1 heading "L|o|c|k|-|W|a|i|t"
col sid       format 9999
col maction format a45 trunc

select       s.username
,            osuser
,            sid
,            decode(status,
                 'ACTIVE','Act',
               'INACTIVE','Inact',
                 'KILLED','Kill', status)      stat
,            decode(type,
           'BACKGROUND','Back',
                 'USER','User', type)          type
,          s.machine
,          s.process
,          p.spid
,          decode(command,
                       0,'',
                       1,'Create Table',
                       2,'Insert',
                       3,'Select',
                       4,'Create Clust',
                       5,'Alter Clustr',
                       6,'Update',
                       7,'Delete',
                       8,'Drop',
                       9,'Create Index',
                      10,'Drop Index',
                      11,'Alter Index',
                      12,'Drop Table',
                      15,'Alter Table',
                      17,'Grant',
                      18,'Revoke',
                      19,'Create Syn',
                      20,'Drop Synonym',
                      21,'Create View',
                      22,'Drop View',
                      26,'Lock Table',
                      27,'nop',
                      28,'Rename',
                      29,'Comment',
                      30,'Audit',
                      31,'Noaudit',
                      32,'Cre Ext Data',
                      33,'Drop Ext Dat',
                      34,'Create Data',
                      35,'Alter Data',
                      36,'Cre Roll Seg',
                      37,'Alt Roll Seg',
                      38,'Drp Roll Seg',
                      39,'Cre Tablesp',
                      40,'Alt Tablesp',
                      41,'Drop Tablesp',
                      42,'Alt Session',
                      43,'Alter User',
                      44,'Commit',
                      45,'Rollback',
                      46,'Save Point',
                      47,'PL/SQL',  to_char(command))   command
,          decode(lockwait,
                       '','', 'Y') lockwait
,          s.module||' '||s.action maction
from   v$session    s
,      v$process    p
where  s.paddr = p.addr
order by spid
,        type desc
,        username
,        osuser
,        sid      ;