Active Sessions with Command

Post date: May 21, 2014 10:39:50 AM

SELECT vs.inst_id, vs.username, vs.sid, vs.serial#, 

         TO_CHAR(vs.logon_time,'DD-MON-YY hh24:mi:ss') CONNECTED, vs.status,

         CASE 

              when vs.command = 0 then 'IDLE'

              when vs.command = 1 then 'CREATE TABLE' 

              when vs.command = 2 then 'INSERT' 

              when vs.command = 3 then 'SELECT'

              when vs.command = 4 then 'CREATE CLUSTER' 

              when vs.command = 5 then 'ALTER CLUSTER'

              when vs.command = 6 then 'UPDATE' 

              when vs.command = 7 then 'DELETE' 

              when vs.command = 8 then 'DROP CLUSTER' 

              when vs.command = 9 then 'CREATE INDEX' 

              when vs.command = 10 then 'DROP INDEX' 

              when vs.command = 11 then 'ALTER INDEX' 

              when vs.command = 12 then 'DROP TABLE' 

              when vs.command = 13 then 'CREATE SEQUENCE' 

              when vs.command = 14 then 'ALTER SEQUENCE' 

              when vs.command = 15 then 'ALTER TABLE'

              when vs.command = 16 then 'DROP SEQUENCE'

              when vs.command = 17 then 'GRANT OBJECT'

              when vs.command = 18 then 'REVOKE OBJECT'

              when vs.command = 19 then 'CREATE SYNONYM'

              when vs.command = 20 then 'DROP SYNONYM'

              when vs.command = 21 then 'CREATE VIEW'

              when vs.command = 22 then 'DROP VIEW'

              when vs.command = 23 then 'VALIDATE INDEX'

              when vs.command = 24 then 'CREATE PROCEDURE'

              when vs.command = 25 then 'ALTER PROCEDURE'

              when vs.command = 26 then 'LOCK' 

              when vs.command = 27 then 'NO-OP'

              when vs.command = 28 then 'RENAME'

              when vs.command = 29 then 'COMMENT'

              when vs.command = 30 then 'AUDIT OBJECT'

              when vs.command = 31 then 'NOAUDIT OBJECT'

              when vs.command = 32 then 'CREATE DATABASE LINK'

              when vs.command = 33 then 'DROP DATABASE LINK'

              when vs.command = 34 then 'CREATE DATABASE'

              when vs.command = 35 then 'ALTER DATABASE'

              when vs.command = 36 then 'CREATE ROLLBACK SEG'

              when vs.command = 37 then 'ALTER ROLLBACK SEG'

              when vs.command = 38 then 'DROP ROLLBACK SEG'

              when vs.command = 39 then 'CREATE TABLESPACE'

              when vs.command = 40 then 'ALTER TABLESPACE'

              when vs.command = 41 then 'DROP TABLESPACE'

              when vs.command = 42 then 'ALTER SESSION'

              when vs.command = 43 then 'ALTER USER'

              when vs.command = 44 then 'COMMIT'

              when vs.command = 45 then 'ROLLBACK'

              when vs.command = 46 then 'SAVEPOINT'

              when vs.command = 47 then 'PL/SQL EXECUTE'

              when vs.command = 48 then 'SET TRANSACTION'

              when vs.command = 49 then 'ALTER SYSTEM'

              when vs.command = 50 then 'EXPLAIN'

              when vs.command = 51 then 'CREATE USER'

              when vs.command = 52 then 'CREATE ROLE'

              when vs.command = 53 then 'DROP USER'

              when vs.command = 54 then 'DROP ROLE'

              when vs.command = 55 then 'SET ROLE'

              when vs.command = 56 then 'CREATE SCHEMA'

              when vs.command = 57 then 'CREATE CONTROL FILE'

              when vs.command = 59 then 'CREATE TRIGGER'

              when vs.command = 60 then 'ALTER TRIGGER'

              when vs.command = 61 then 'DROP TRIGGER'

              when vs.command = 62 then 'ANALYZE TABLE'

              when vs.command = 63 then 'ANALYZE INDEX'

              when vs.command = 64 then 'ANALYZE CLUSTER'

              when vs.command = 65 then 'CREATE PROFILE'

              when vs.command = 66 then 'DROP PROFILE' 

              when vs.command = 67 then 'ALTER PROFILE' 

              when vs.command = 68 then 'DROP PROCEDURE' 

              when vs.command = 70 then 'ALTER RESOURCE COST' 

              when vs.command = 71 then 'CREATE SNAPSHOT LOG' 

              when vs.command = 72 then 'ALTER SNAPSHOT LOG' 

              when vs.command = 73 then 'DROP SNAPSHOT LOG' 

              when vs.command = 74 then 'CREATE SNAPSHOT' 

              when vs.command = 75 then 'ALTER SNAPSHOT' 

              when vs.command = 76 then 'DROP SNAPSHOT' 

              when vs.command = 77 then 'CREATE TYPE' 

              when vs.command = 78 then 'DROP TYPE' 

              when vs.command = 79 then 'ALTER ROLE' 

              when vs.command = 80 then 'ALTER TYPE' 

              when vs.command = 81 then 'CREATE TYPE BODY' 

              when vs.command = 82 then 'ALTER TYPE BODY' 

              when vs.command = 83 then 'DROP TYPE BODY' 

              when vs.command = 84 then 'DROP LIBRARY' 

              when vs.command = 85 then 'TRUNCATE TABLE' 

              when vs.command = 86 then 'TRUNCATE CLUSTER' 

              when vs.command = 91 then 'CREATE FUNCTION'

              when vs.command = 92 then 'ALTER FUNCTION' 

              when vs.command = 93 then 'DROP FUNCTION' 

              when vs.command = 94 then 'CREATE PACKAGE' 

              when vs.command = 95 then 'ALTER PACKAGE' 

              when vs.command = 96 then 'DROP PACKAGE' 

              when vs.command = 97 then 'CREATE PACKAGE BODY' 

              when vs.command = 98 then 'ALTER PACKAGE BODY' 

              when vs.command = 99 then 'DROP PACKAGE BODY' 

              when vs.command = 100 then 'LOGON' 

              when vs.command = 101 then 'LOGOFF' 

              when vs.command = 102 then 'LOGOFF BY CLEANUP' 

              when vs.command = 103 then 'SESSION REC' 

              when vs.command = 104 then 'SYSTEM AUDIT' 

              when vs.command = 105 then 'SYSTEM NOAUDIT' 

              when vs.command = 106 then 'AUDIT DEFAULT' 

              when vs.command = 107 then 'NOAUDIT DEFAULT' 

              when vs.command = 108 then 'SYSTEM GRANT' 

              when vs.command = 109 then 'SYSTEM REVOKE' 

              when vs.command = 110 then 'CREATE PUBLIC SYNONYM' 

              when vs.command = 111 then 'DROP PUBLIC SYNONYM' 

              when vs.command = 112 then 'CREATE PUBLIC DATABASE LINK' 

              when vs.command = 113 then 'DROP PUBLIC DATABASE LINK' 

              when vs.command = 114 then 'GRANT ROLE' 

              when vs.command = 115 then 'REVOKE ROLE' 

              when vs.command = 116 then 'EXECUTE PROCEDURE' 

              when vs.command = 117 then 'USER COMMENT' 

              when vs.command = 118 then 'ENABLE TRIGGER' 

              when vs.command = 119 then 'DISABLE TRIGGER' 

              when vs.command = 120 then 'ENABLE ALL TRIGGERS' 

              when vs.command = 121 then 'DISABLE ALL TRIGGERS' 

              when vs.command = 122 then 'NETWORK ERROR' 

              when vs.command = 123 then 'EXECUTE TYPE' 

              when vs.command = 157 then 'CREATE DIRECTORY' 

              when vs.command = 158 then 'DROP DIRECTORY' 

              when vs.command = 159 then 'CREATE LIBRARY' 

              when vs.command = 160 then 'CREATE JAVA' 

              when vs.command = 161 then 'ALTER JAVA' 

              when vs.command = 162 then 'DROP JAVA' 

              when vs.command = 163 then 'CREATE OPERATOR' 

              when vs.command = 164 then 'CREATE INDEXTYPE' 

              when vs.command = 165 then 'DROP INDEXTYPE' 

              when vs.command = 167 then 'DROP OPERATOR' 

              when vs.command = 168 then 'ASSOCIATE STATISTICS' 

              when vs.command = 169 then 'DISASSOCIATE STATISTICS' 

              when vs.command = 170 then 'CALL METHOD' 

              when vs.command = 171 then 'CREATE SUMMARY' 

              when vs.command = 172 then 'ALTER SUMMARY' 

              when vs.command = 173 then 'DROP SUMMARY' 

              when vs.command = 174 then 'CREATE DIMENSION' 

              when vs.command = 175 then 'ALTER DIMENSION' 

              when vs.command = 176 then 'DROP DIMENSION' 

              when vs.command = 177 then 'CREATE CONTEXT' 

              when vs.command = 178 then 'DROP CONTEXT' 

              when vs.command = 179 then 'ALTER OUTLINE' 

              when vs.command = 180 then 'CREATE OUTLINE' 

              when vs.command = 181 then 'DROP OUTLINE'

              when vs.command = 182 then 'UPDATE INDEXES' 

              when vs.command = 183 then 'ALTER OPERATOR' 

              else 'EEK'

         END as COMMAND,  vs.osuser, vs.machine, vs.program, vs.module

FROM gv$session vs

where vs.status = 'ACTIVE'

and type = 'USER'