| |
 |
|
Oracle v$session Tips
Oracle Database Tips by Donald Burleson |
Question: How to I use v$session to find the server user ID
and other information from the dictionary? Is
there more than v$session?
Answer: Here is a good script to
display all Oracle session details including the osuser and
server name. The script joins v$session and v$process to get
the PID. The PID can be joined into other views (v$sql_plan)
to get more details for each Oracle user session.
Also see how to query
SQL details (rows
processed) for a specific user session.
If you don't want to write your own v$ scripts, you
can download over 600 dictionary scripts in the
Oracle scripts
collection.
Viewing complete session information
Most Oracle professionals know that they can query
the v$session view to see the current sessions. However,
instead of v$session you can also interrogate the
v$sysstat view to see the current logons, cumulative logons since
startup, and the high-water-mark of logons since startup time.
The following v$session script provides a wealth of
information about the number of sessions on your Oracle database.
rem session.sql - displays
all connected sessions
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;
select
rpad(c.name||':',11)||rpad('
current logons='||
(to_number(b.sessions_current)),20)||'cumulative
logons='||
rpad(substr(a.value,1,10),10)||'highwater
mark='||
b.sessions_highwater
Information
from
v$sysstat a,
v$license b,
v$database c
where
a.name = 'logons cumulative'
;
ttitle "dbname Database|UNIX/Oracle
Sessions";
set heading off;
select 'Sessions on database
'||substr(name,1,8) from v$database;
set heading on;
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10)
username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30)
program
from v$session b, v$process
a
where
see
code depot for full script
b.paddr = a.addr
and type='USER'
order by spid;
ttitle off;
set heading off;
select 'To kill, enter
SQLPLUS> ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';'
from dual;
spool off;
|
|
|
and include the URL for the page.
