Oracle Apps V$Session Tables: Find Session info

V$SESSION
This view lists session information for each current session.

V$PROCESS
This view contains information about the currently active processes. While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.

V$SESSION_WAIT
V$SESSION_WAIT displays the current or last wait for each session.

V$SESSION_LONGOPS
This view displays the status of various operations that run for longer than 6 seconds (in absolute time).


Oracle standard scripts.
Session_waits.sql script uses the v$session_wait view to display a list of the events currently being waited on by active sessions.  This is useful when trying to discover what each active session is currently doing.


session_waits.sql

SET LINESIZE 200
SET PAGESIZE 1000

COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15

SELECT NVL(s.username, '(oracle)') AS username,
       s.sid,
       s.serial#,
       sw.event,
       sw.wait_class,
       sw.wait_time,
       sw.seconds_in_wait,
       sw.state
FROM   v$session_wait sw,
       v$session s
WHERE  see code depot for full script
s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;  

longops.sql
The use of the dbms_application_info package to register long operations was discussed in a previous section.  The longops.sql scripts uses the v$session_longops view to identify and monitor sessions performing long operations, including those registered using the dbms_application_info package.


COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE s.sid     = sl.sid
AND    s.serial# = sl.serial#;


Sessions.sql
The sessions.sql script uses the v$session view to provide information about all the sessions currently connected to the database instance.  In addition, the v$process view is used to provide the associated operating system process id (SPID) for each session.

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
       v$process p
WHERE s.paddr = p.addr
ORDER BY s.username, s.osuser;