These handy SQL scripts will display user session information in Oracle.
You may need Dictionary privileges in order to execute these.
SELECT * FROM V$SESSION ;
set heading on
set pagesize 200
set newpage 0
SELECT T1.USERNAME, T2.TABLESPACE, T2.CONTENTS, T2.EXTENTS, T2.BLOCKS
FROM V$SESSION T1, V$SORT_USAGE T2
WHERE T1.SADDR = T2.SESSION_ADDR ;
SELECT i.instance_name, s.sid, s.serial#
FROM v$session s, v$instance i
WHERE s.saddr in
(SELECT t.ses_addr
FROM V$transaction t, dba_rollback_segs r
WHERE t.xidusn=r.segment_id
AND r.tablespace_name='RBS') ;
column action format a12
SELECT
instance_name,
SID, -- NUMBER
SERIAL#, -- NUMBER
USER#, -- NUMBER
substr(USERNAME,1,10) as "username", -- VARCHAR2(30)
substr(SCHEMANAME,1,10) as "schema", -- VARCHAR2(30)
substr(machine,1,18) as "machine",
terminal,
substr(PROGRAM,1,10) as "program", -- VARCHAR2(48)
COMMAND, -- NUMBER
upper(decode(nvl(COMMAND, 0),
0, '---------------',
1, 'Create Table',
2, 'Insert ...',
3, 'Select. ..',
4, 'Create Cluster',
5, 'Alter Cluster',
6, 'Update. ..',
7, 'Delete. ..',
8, 'Drop. ..',
9, 'Create Index',
10, 'Drop Index',
11, 'Alter Index',
12, 'Drop Table',
13, '--',
14, '--',
15, 'Alter Table',
16, '--',
17, 'Grant',
18, 'Revoke',
19, 'Create Synonym',
20, 'Drop Synonym',
21, 'Create View',
22, 'Drop View',
23, '--',
24, '--',
25, '--',
26, 'Lock Table',
27, 'No Operation',
28, 'Rename',
29, 'Comment',
30, 'Audit',
31, 'NoAudit',
32, 'Create Ext DB',
33, 'Drop Ext. DB',
34, 'Create Database',
35, 'Alter Database',
36, 'Create RBS',
37, 'Alter RBS',
38, 'Drop RBS',
39, 'Create Tablespace',
40, 'Alter Tablespace',
41, 'Drop tablespace',
42, 'Alter Session',
43, 'Alter User',
44, 'Commit',
45, 'Rollback',
46, 'Savepoint')) job,
LOCKWAIT, -- VARCHAR2(8)
t1.STATUS, -- VARCHAR2(8)
PROCESS, -- VARCHAR2(9)
TYPE, -- VARCHAR2(10)
LOGON_TIME, -- DATE
ACTION, seconds_in_wait
FROM v$session t1, v$instance
order by USERNAME, SERIAL#
/
select username,count(1) from v$session group by username
/
|
|
