See who is logged into the database

This script is used to see who is logged into the database.

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

This was first published in June 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close