v$session: Session deatils in oracle

/* Formatted on (Formatter Plus v4.8.8) */
SET lines 120

COLUMN sid format a5

COLUMN client format a13

COLUMN inlogtijd heading logon_time

COLUMN inlogtijd format a18

COLUMN ora_user heading oracle_user

COLUMN ora_user format a12

COLUMN serial# format a5

COLUMN serial# heading ser#

COLUMN programma format a35

COLUMN computer format a17

SELECT DECODE (s.TYPE, 'BACKGROUND', 'INTERNAL', username) ora_user,
SUBSTR
(DECODE (UPPER (SUBSTR (s.terminal, 1, 3)),
'PTS', machine || ':' || s.osuser,
DECODE (SUBSTR (s.terminal, 1, 1),
'?', s.machine || ':' || s.osuser,
DECODE (s.terminal,
NULL, s.machine
|| DECODE (s.osuser, NULL, '', ':')
|| s.osuser,
DECODE (s.terminal,
'DOS PC', s.terminal,
SUBSTR (s.machine,
INSTR (s.machine,
'\',
1
)
+ 1,
6
)
|| ':'
|| s.osuser
)
)
)
),
1,
13
) client,
DECODE (SUBSTR (s.machine, INSTR (s.machine, '\', 1) + 1, 6),
'W12021', 'Edwin Korsten',
s.machine
) computer,
TO_CHAR (SID) SID, TO_CHAR (serial#) serial#,
TO_CHAR (logon_time, 'DD-MM-YYYY HH24:MI') logon_time,
SUBSTR (DECODE (lockwait,
NULL, DECODE (command,
0, 'NO OP',
1, 'CR TAB',
2, 'INSERT',
3, 'SELECT',
4, 'CR CLU',
5, 'AL CLU',
6, 'UPDATE',
7, 'DELETE',
8, 'DROP',
9, 'CR IND',
10, 'DR IND',
11, 'AL IND',
12, 'DR TAB',
13, 'CR SEQ',
14, 'AL SEQ',
15, 'AL TAB',
16, 'DR SEQ',
17, 'GRANT',
18, 'REVOKE',
19, 'CR SYN',
20, 'DR SYN',
21, 'CR VIW',
22, 'DR VIW',
23, 'VAL IND',
24, 'CR PRC',
25, 'AL PRC',
26, 'LK TAB',
27, 'NO-OPE',
28, 'RENAME',
29, 'COMMEN',
30, 'AUDIT',
31, 'NOAUD',
32, 'CR DBLNK',
33, 'DR DBLNK',
34, 'CR DB',
35, 'AL DB',
36, 'CR RBS',
37, 'AL RBS',
38, 'DR RBS',
39, 'CR TSP',
40, 'AL TSP',
41, 'DR TSP',
42, 'AL SES',
43, 'AL USR',
44, 'COMMIT',
45, 'ROLLBCK',
46, 'SAVEPNT',
47, 'EX PLS',
48, 'SET TRN',
49, 'SW LOG',
50, 'EXPLAIN',
51, 'CR USR',
52, 'CR ROL',
53, 'DR USR',
54, 'DR ROL',
55, 'SET ROL',
56, 'CR SCH',
57, 'CR CFIL',
58, 'AL TRC',
59, 'CR TRG',
60, 'AL TRG',
61, 'DR TRG',
62, 'AN TAB',
63, 'AN IND',
64, 'AN CLU',
65, 'CR PROF',
67, 'DR PROF',
68, 'AL PROF',
69, 'DR PRC',
70, 'AL RC',
71, 'CR SNLOG',
72, 'AL SNLOG',
73, 'DR SNLOG',
74, 'CR SNSHT',
75, 'AL SNSHT',
76, 'DR SNSHT',
79, 'AL ROL',
85, 'TRN TAB',
86, 'TRN CLU',
88, 'AL VW',
91, 'CR FNC',
92, 'AL FNC',
93, 'DR FNC',
94, 'CR PCK',
95, 'AL PCK',
96, 'DR PCK',
97, 'CR PCKB',
98, 'AL PCKB',
99, 'DR PCKB',
TO_CHAR (command)
),
'WAITING'
),
1,
10
) command
FROM v$session s
ORDER BY username;