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.
#!/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 ""
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
This was first published in June 2006