Active Sessions with Command

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

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

	Search this site