#!/bin/sh

#
# (c) spd_at_daphne.cps.unizar.es
# <http://home.comcast.net/~arivenes/sql/dispactive.sql>
# Fri Oct  7 11:14:43 MEST 2005
# License: Cantoware - Si te gusta ya te puedes dar con un canto en los dientes
#
# This script will list active oracle useres
# Needs: root privileges to be able to "su oracle"
# Use: ./ora-who.sh
# Depends: ora-common.sh
#



usage()
{
    echo "Use: `basename $0` [-h] name"
    echo "-h: help"
}

    
set -- `getopt h $*`
for i in $*
do  
    case $i in
    -h)
        usage
        exit 0
        ;; 
	--) shift; break;;
    esac
done

. $HOME/usr/sbin/ora-common.sh

su $ORACLE_OWNER -c "$CMD $ARGS" 2>&1 <<++
$INIT
--   REQUIREMENTS:
--
--
--   MODIFICATIONS:
--      A. Rivenes, 01/27/2000, Added Oracle PID
--      A. Rivenes, 04/23/2004, Increased the column width for the client
--      process.
--      A. Rivenes, 08/25/2005, Added client_identifier, client_info, module,
--      action, and service_name columns, commented out
--      PID, osuser, server, process and program.
--
--
SET PAGESIZE 9999;
SET VERIFY off;
SET FEEDBACK off;
SET LINESIZE 132;
--
COLUMN uname      HEADING  'User|Name'               FORMAT A11;
COLUMN sid        HEADING  'Oracle|SID'              FORMAT 999999;
COLUMN pid        HEADING  'Oracle|PID'              FORMAT 999999;
COLUMN sernum     HEADING  'Serial#'                 FORMAT 999999;
COLUMN spid       HEADING  'Server|PID'              FORMAT 999999;
COLUMN suser      HEADING  'Client|OS User'          FORMAT A8;
COLUMN clid       HEADING  'Client|Identifier'       FORMAT A10;
COLUMN status     HEADING  'Status'                  FORMAT A8;
COLUMN server     HEADING  'Server'                  FORMAT A9;
COLUMN smach      HEADING  'Client|Machine'          FORMAT A15   WORD_WRAPPED;
COLUMN process    HEADING  'Client|Process'          FORMAT A9;
COLUMN sprog      HEADING  'Program'                 FORMAT A15   WORD_WRAPPED;
COLUMN clinfo     HEADING  'Client|Info'             FORMAT A15   WORD_WRAPPED;
COLUMN mod        HEADING  'Module'                  FORMAT A9;
COLUMN act        HEADING  'Action'                  FORMAT A9;
COLUMN srvnm      HEADING  'Service|Name'            FORMAT A15   WORD_WRAPPED;
--
SELECT a.username uname,
       a.sid sid,
--       b.pid pid,
       a.serial# sernum,
       b.spid spid,
--       a.osuser suser,
       a.status status,
       a.client_identifier clid,
--       a.server server,
       a.machine smach,
--       a.process process,
--       a.program sprog,
       a.client_info clinfo,
       a.module mod,
       a.action act
  FROM v\$session a, v\$process b WHERE a.paddr = b.addr
 AND a.type != 'BACKGROUND';
++