Counting Online Oracle Users

[Tony Williams]

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?

[SHABU KHAN]

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]

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

Something like this:

[A. Clay Stephenson]

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.

[Tony Williams]

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]

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]

Thanks Manoj,

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

[MANOJ SRIVASTAVA]

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]

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]

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