출처: target=_blank>http://shahiddba.blogspot.kr/2012/04/oracle-dba-daily-checklist.html


1. Average Wait Time for Particular Event:
SELECT EVENT,  TOTAL_WAITS,  TOTAL_TIMEOUTS,  TIME_WAITED, round(AVERAGE_WAIT,2) "Average Wait"
from v$system_event order    by TOTAL_WAITS;

2. Sessions Waiting On A Particular Wait Event:
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0 AND event NOT IN ('smon timer','pipe get','wakeup time manager', 'pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event ORDER BY 1 DESC;

3. Track Logon time of DB user and OS user:
Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine from v$session where type !='BACKGROUND'; ‎

4. Track all Session User Details:
select sid, serial#,machine, status, osuser,username from v$session where username!='NULL';

5. Track Active Session User Details:
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status= 'ACTIVE' AND UserName IS NOT NULL;

6. Track Active User Details:
SELECT s.inst_id,  s.sid,  s.serial#,  p.spid,  s.username,  s.program FROM gv$session s  JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND';

7. Show Username and SID/SPID with Program Name:
select sid,name,value from v$spparameter where isspecified='TRUE';‎

SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session
WHERE Status= 'ACTIVE' AND UserName IS NOT NULL;  --to find active session

SELECT s.inst_id,  s.sid,  s.serial#,  p.spid,  s.username,  s.program    --active users details
FROM gv$session s  JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';