This is a script that was passed down to me about eight years ago from another DBA and I have passed it on to others. I use it all the time to see who is logged into the database. I use it when I have to kill an OS process for a user connected to the database or just to see who is on.
To run, I log on to the database server and set my Oracle environment for the database I want to check using coraenv or oraenv. Then, I just type "on_ora_sid". This script runs a SQL script called "session.sql" and will query the Oracle V$Session tables and return the userid of people logged into the Oracle database identified SID.
on_ora_sid:
#!/bin/csh -f
# This program will query the Oracle V$Session tables and return
# the userid of people logged in to the Oracle Database identified
# oracle_sid
#
echo ""
#
echo ""
setenv ORACLE_SID $ORACLE_SID
echo "Users logged in to" $ORACLE_SID
sleep 3
sqlplus system/password @/ora01/scripts/dba_scripts/session.sql
echo ""
session.sql:
column username heading user format a10
column program heading running format a40
column status heading status format a9
column spid heading proc format a5
column lockwait heading lockwait format a8
select sys.v_$session.username,
sys.v_$session.program,
sys.v_$session.status,
sys.v_$session.lockwait,
sys.v_$process.spid
from sys.v_$session, sys.v_$process
where sys.v_$session.paddr = sys.v_$process.addr
and sys.v_$session.username is not null;
exit
