Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
cancel
Showing results for 
Search instead for 
Did you mean: 

Counting Online Oracle Users

SOLVED
Go to solution
Tony Williams
Regular Advisor

Counting Online Oracle Users

Is there a way using the List Open Files (LSOF) application, or any way from the HP-UX command prompt to count the number of active users connecting into an Oracle Database?
10 REPLIES
SHABU KHAN
Trusted Contributor

Re: Counting Online Oracle Users

Tony,

At the Unix level ... Yes,

do this:

prompt>ps -ef|grep oracle
you will see all the oracle daemons and connections... look for the one with (LOCAL=NO ... or Description) ... those are the actual application/user connections ...then grab that particular PID and then do a:

prompt> lsof -p | grep TCP

you will see who is connected ...

Hope this helps !

Thanks,
Shabu
A. Clay Stephenson
Acclaimed Contributor

Re: Counting Online Oracle Users

You will do far better to write a sqlplus or svrmgrl query and examine the v$session view.

Something like this:
If it ain't broke, I can fix that.
A. Clay Stephenson
Acclaimed Contributor

Re: Counting Online Oracle Users

Oops, hit the button too soon.

select unique process, osuser from v$session where osuser <> 'oracle';

You can script this up and then call from within a shell script if you like. You should probably execute sqlpus (or svrmgrl) abd do a 'desc v$session' to see what other data you are interested in such as connections from a particular host.
If it ain't broke, I can fix that.
Tony Williams
Regular Advisor

Re: Counting Online Oracle Users

Thanks Shabu,

I wonder though, doesn't Oracle pre-process a number of Oracle connections that may not necessarily have a live user connected to the port. The port is open but is there a user on it?

Mr. Stephenson,

This will require a bit of effort on my part, because there are several instances on my server, that I don't think anyone is using, so I would like to get rid of them. However, in order to script this out I suspect I have to log in as each individual oracle admin for that instance.

Thanks.
MANOJ SRIVASTAVA
Honored Contributor

Re: Counting Online Oracle Users

Hi Tony


How about getting lsof itself for the box , there are binaries availabe for both 32 bit and 64 bit version of HPUX at :

ftp://vic.cc.purdue.edu/pub/tools/unix/lsof

and you are good to go.

Manoj Srivastava
Tony Williams
Regular Advisor

Re: Counting Online Oracle Users

Thanks Manoj,

We have lsof 4.61 loaded, but I think that is 32-bit only.
MANOJ SRIVASTAVA
Honored Contributor

Re: Counting Online Oracle Users

Hi Tony

There is 64 bit binary there , but incase you want i can email you the compiled one from one of our system as an attachment . I am at msrivast@telecorp1.com .


Manoj Srivastava
SHABU KHAN
Trusted Contributor

Re: Counting Online Oracle Users

Tony,
You are Welcome !

I see what you are saying ..

You could script this as follows:

for PID in `ps -ef|grep oracle| awk '/LOCAL/{print $2}' | grep -v grep`
do
lsof -p"${PID}" | grep TCP
done

You could further enhance this script with more validations ..

Hope this helps !

Thanks,
-Shabu
SHABU KHAN
Trusted Contributor

Re: Counting Online Oracle Users

Tony,

Try this:

prompt>cat oracle_active_users

#!/bin/ksh

for PID in `ps -ef|grep oracle| awk '/LOCAL/{print $2}' | grep -v grep`
do
lsof -p"${PID}" | grep TCP | grep -v -i "idle"
done

prompt>chmod 755 oracle_active_users

prompt>./oracle_active_users | wc -l

should give you the total active/established connections to your oracle
database. This can take very long ...

As Clay mentioned earlier doing it at the Database level is much easier and efficient.

Good Luck.

Thanks,
Shabu
Alexander M. Ermes
Honored Contributor
Solution

Re: Counting Online Oracle Users

Hi there.
Why not putting Clay's idea into a little sql-script and start it from the shell using :

su - oracle -c 'sqlplus user/passwd @user_check.sql -s

You can start this from the cron for the different instances with the ORACLE_SID as a parameter and spool it to some temp file.

SQL :

set termout off
set feedback off
set pages 65
set lines 60
ttitle " Free space report "
column "XYZ" format a20
column "ABC" format 99999.99
select unique process, osuser from v$session where osuser <> 'oracle';
spool /var/tmp/usercheck&&2..lst.&&1
;
spool off
set termout on
set feedback on
exit


Shellscript :

#!/sbin/sh
# ==========================================================================
#
#
# set -x
#
# setting the proper PATH.....
PATH=$PATH:/usr/local/bin
export PATH
#
TAG_=`date +'%y%m%d%H%M'`; export TAG_
#
# setting the Oracle environment
name=$1

ORACLE_SID=${name}; export ORACLE_SID
ORACLE_HOME=something; export ORACLE_HOME
ORAENV_ASK=NO; export ORAENV_ASK

. /usr/local/bin/oraenv
. /usr/local/bin/${name}.env
# echo $TAG_
echo "^L" >> /var/tmp/usercheck$ORACLE_SID.lst

echo ${1} >> /var/tmp/usercheck$ORACLE_SID.lst.02*
sqlplus system/manager @/directory/usercheck.sql $TAG_ $ORACLE_SID -s
echo " " >> /var/tmp/usercheck$ORACLE_SID.lst
echo " " >> /var/tmp/usercheck$ORACLE_SID.lst
date >> /var/tmp/usercheck$ORACLE_SID.lst
/bin/cat /var/tmp/usercheck$ORACLE_SID.lst.02* >> /var/tmp/free$ORACLE_SID.lst
cat /var/tmp/usercheck$ORACLE_SID.lst.02*
rm /var/tmp/usercheck$ORACLE_SID.lst.02*


Try this.
hope it helps
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"