topic Re: Counting Online Oracle Users in Operating System - HP-UX

Counting Online Oracle Users

Tony Williams — Fri, 19 Apr 2002 16:59:26 GMT

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?

Re: Counting Online Oracle Users

SHABU KHAN — Fri, 19 Apr 2002 17:40:56 GMT

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

Re: Counting Online Oracle Users

A. Clay Stephenson — Fri, 19 Apr 2002 17:45:34 GMT

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

Something like this:

Re: Counting Online Oracle Users

A. Clay Stephenson — Fri, 19 Apr 2002 17:52:43 GMT

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.

Re: Counting Online Oracle Users

Tony Williams — Fri, 19 Apr 2002 20:05:47 GMT

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.

Re: Counting Online Oracle Users

MANOJ SRIVASTAVA — Fri, 19 Apr 2002 20:17:33 GMT

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

Re: Counting Online Oracle Users

Tony Williams — Fri, 19 Apr 2002 20:47:48 GMT

Thanks Manoj,

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

Re: Counting Online Oracle Users

MANOJ SRIVASTAVA — Fri, 19 Apr 2002 20:49:52 GMT

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

Re: Counting Online Oracle Users

SHABU KHAN — Fri, 19 Apr 2002 22:42:51 GMT

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

Re: Counting Online Oracle Users

SHABU KHAN — Fri, 19 Apr 2002 22:55:50 GMT

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

Re: Counting Online Oracle Users

Alexander M. Ermes — Sat, 20 Apr 2002 08:37:24 GMT

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