Oracle Technology‎ > ‎Database‎ > ‎database notes‎ > ‎

Active Sessions with Command

posted 21 May 2014, 03:39 by Alan Nolan-Davies
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'
Comments