DATABASE USER STATISTICS

SELECT username, VALUE || ' ' || 'bytes' "Current UGA memory"
FROM v_$session sess, v_$sesstat sstat, v_$statname sname
WHERE sess.SID = sstat.SID
AND sstat.statistic# = sname.statistic#
AND sname.NAME = 'session uga memory';


SELECT COUNT (*) "ACTIVE USERS"
FROM v_$session
WHERE username IS NOT NULL;


SELECT SUBSTR (s.username, 1, 15) username, SUBSTR (s.status, 1, 8) status,
SUBSTR (s.server, 1, 10) server, SUBSTR (s.TYPE, 1, 10) TYPE,
SUBSTR (s.event, 1, 20) "WAIT EVENT",
DECODE (s.command,
1, 'Create Table',
2, 'Insert',
3, 'Select',
6, 'Update',
7, 'Delete',
8, 'Drop',
9, 'Create Index',
10, 'Drop Index',
12, 'Drop Table',
17, 'Grant',
26, 'Lock Table',
42, 'Alter Session',
43, 'Alter User',
44, 'Commit',
45, 'Rollback',
s.command
) command
FROM v_$session s, v_$session_wait w
WHERE (s.SID = w.SID) AND s.username != 'SYS'
ORDER BY s.username;



SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS",
u.PROFILE, r.granted_role, r.admin_option, r.default_role
FROM SYS.dba_users u, SYS.dba_role_privs r
WHERE u.username = r.grantee(+)
GROUP BY u.username,
u.default_tablespace,
u.temporary_tablespace,
u.PROFILE,
r.granted_role,
r.admin_option,
r.default_role;



SELECT USER, osuser
FROM gv_$session
WHERE SID = (SELECT SID
FROM gv$mystat
WHERE ROWNUM = 1);


SELECT SID, osuser, username, status,
TO_CHAR (logon_time, 'DAY HH24:MI:SS') logon_time,
FLOOR (last_call_et / 3600)
|| ':'
|| FLOOR (MOD (last_call_et, 3600) / 60)
|| ':'
|| MOD (MOD (last_call_et, 3600), 60) idle,
program
FROM v_$session
WHERE username IS NOT NULL
ORDER BY last_call_et;



SELECT SID, serial#, SUBSTR (username, 1, 10) NAME,
SUBSTR (machine, 1, 10) computer, command, status,
SUBSTR (osuser, 1, 8) osuser, process, program
FROM v_$session
ORDER BY NAME;



SELECT SID, authentication_type, osuser, network_service_banner
FROM gv_$session_connect_info;


SELECT *
FROM gv$pwfile_users;