gwhoson.sql

--
-- Title       :   gwhoson.sql
-- Description :   To list current database connections details
--                 across a multiple server RAC environment.
--
-- Copyright   :   ABCdba.com 2007
--
 
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 node HEA "Nod" FOR a3
 
SELECT
   ' '||NVL( s.username, '    ????    ' ) orauser,
   ' '||LPAD( s.inst_id, 1 )||' ' node,
   ' '||s.osuser osuser,
   LPAD( s.sid, 4 ) ssid, LPAD( s.serial#, 6 ) sserial,
   LPAD( p.spid, 6 ) ospid,
   INITCAP( LOWER( TO_CHAR( s.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
FROM
   gv$session s, gv$process p,
   (
   SELECT
      DISTINCT sid, inst_id, authentication_type
   FROM
      gv$session_connect_info
   ) sc
WHERE
   ( s.paddr = p.addr AND s.inst_id = p.inst_id )
AND
   ( s.sid = sc.sid AND s.inst_id = sc.inst_id )
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