whoson.sql

-- 
-- Title :       whoson.sql 
-- Description : To list current database 
--               connections details 
-- 
-- Usage/Notes : 
-- 
-- Copyright :   ABCdba.com 2002 
-- 
 
SET TERMOUT OFF 
STORE SET save_env.sql REPLACE 
SET TERMOUT ON 
 
CLEAR BREAKS 
CLEAR COLUMNS 
CLEAR COMPUTES 
 
SET PAGESIZE 20 
SET PAUSE ON 
SET PAUSE ' Press <<RETURN>> for next page...' 
 
COL orauser HEA " Oracle User " FOR a17 TRUNC 
COL osuser HEA " O/S User " FOR a10 TRUNC 
COL ssid HEA "Sid" FOR a4 
COL sserial HEA "Serial#" FOR a7 
COL ospid HEA "O/S Pid" FOR a7 
COL slogon HEA " Logon Time " FOR a14 
COL sstat HEA "Status" FOR a6 
COL auth HEA "Auth" FOR a4 
COL conn HEA "Con" FOR a3 
 
SELECT 
' '||NVL( s.username, ' ???? ' ) orauser, 
' '||s.osuser osuser, 
LPAD( s.sid, 4 ) ssid, 
LPAD( s.serial#, 6 ) sserial, 
LPAD( p.spid, 6 ) ospid, 
INITCAP( LOWER( TO_CHAR( logon_time, 
'MONDD HH24:MI:SS' ) ) ) slogon, 
DECODE( s.status, 'ACTIVE', ' Busy ', 
'INACTIVE', ' Idle ', 'KILLED', ' Kill ', 
' ?? ' ) sstat, 
DECODE( sc.authentication_type, 'DATABASE', 
' DB ', 'OS', ' OS ', ' ?? ' ) auth, 
DECODE( s.server, 'DEDICATED', 'Dir', 
'NONE', 'Mts', 'SHARED', 'Mts', '???' ) conn 
FROM 
v$session s, v$process p, 
( 
SELECT 
DISTINCT sid, authentication_type 
FROM 
v$session_connect_info 
) sc 
WHERE 
s.paddr = p.addr AND s.sid = sc.sid 
ORDER BY 
s.status,s.sid 
/ 
 
@save_env.sql 
host rm save_env.sql 
SET TERMOUT ON

© copyright 2001-2014 ABCdba.com | all rights reserved