connection numbers from Oracle

[GoSooJJ]

Hi there,

how can I see number of connections/processes in Oracle? I ran some sql statement long time ago to get this information but I forgot.

Thank you.

 

[JVZ]

I think you may have posted this in the wrong forum...I'm not a DBA...but heres what is I found out for you. Also you may want to post this question on the oracle form...there may be a better SQL script to do what you want to do...

REM DESCRIPTION: This script runs at the SQL prompt and gives the text
REM of the SQL being currently run on the machine. Also gives the SID
REM and Serial# which may be used to killl the session by using the
REM following at the SQL prompt.
REM
REM ALTER SYSTEM KILL SESSION 'SID,SERIAL#'
REM
REM The script starts here:

break on sid skip 1 on username
column sid format 999
column username format a10
select a.sid,a.serial#,a.username,b.sql_text
from v$session a,v$sqltext b
where a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by 1,2,b.piece;
REM End of script

****

After speaking w/DBA that I work with..she provide the following:

One problem with this script(the one above) is that many times the running session is an operation system script. In this situation, you need to kill the session also on the Unix side. To do so, you need to know the operation system session id. That information is given by the audsid column in the v$session. So I use a better script to find out who is running SQL on the database. This script shows only the beginning of the SQL text, and it's useful for the monitoring process.

select decode(sum(decode(s.serial#,l.serial#,1,0)),0,'No','Yes') " ",
s.sid "Session ID",s.status "Status",
s.username "Username", RTRIM(s.osuser) "OS User",
b.spid "OS Process ID",s.machine "Machine Name",
s.program "Program",c.sql_text "SQL text"
from v$session s, v$session_longops l,v$process b,
(select address,sql_text from v$sqltext where piece=0) c
where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address
group by s.sid,s.status,s.username,s.osuser,s.machine,
s.program,b.spid, b.pid, c.sql_text order by s.sid

******* Here is what I found on the Net ********

# This script associates Unix monitoring to Oracle monitoring
# and does in one shot what many DBAs have learnt to do manually:
# when a server is heavily loaded, and when oracle processes
# seem to be the culprits, those processes usually are oracle
# shadow processes which are difficult to relate to client processes.
# You must get the Unix process id, and then query the dynamic
# views. More often than not, once you have noticed the load, identified
# the process(es), connected to Oracle, typed your query, and executed it,
# the peak is over. Note that this script will strictly have the same
# problems on an overloaded machine, but as it is likely to run faster
# than you type, it increases the odds of catching something meaningful.
#

typeset facility=$(basename $0)
typeset SERVICE_NAME=""
typeset PASSWORD=""
typeset USERNAME=""
typeset ORA_CONNECT="/"

usage() {
echo ""
echo "NAME"
echo ""
echo " $facility - Monitor Oracle process load. Associate with client process"
echo ""
echo "SYNOPSIS"
echo ""
echo " $facility [-h] [-u username] [-p password] [-s Oracle sid]"
echo ""
echo "DESCRIPTION"
echo ""
echo " The $facility Korn Shell Script associates Unix monitoring to Oracle"
echo " monitoring."
echo " If no parameters are issued then the default connection string of / is"
echo " used. The Oracle user to whom you are connecting must have access to"
echo " V$... dynamic views."
echo " You are advised to use an externally identified Oracle connection"
echo " (connection by default) otherwise execute the script using the"
echo " parameters indicated below"
echo ""
echo "OPTIONS"
echo ""
echo " -h this Help text"
echo " -u Oracle username to connect to"
echo " -p Password of the user to which you are connecting"
echo " -s ORACLE_SID corresponding to the connection database."
echo ""
echo "AUTHOR"
echo ""
echo " Copyright (C) Oriole Software, 1999 "
echo ""
exit $1
}

# GET THE PARAMETERS

while getopts hp:s:u: option
do
case "$option" in
h) usage;;
p) PASSWORD="$OPTARG";;
s) SERVICE_NAME="$OPTARG";;
u) USERNAME="$OPTARG";;
esac
done;

if [ "$USERNAME" != "" ] ; then
ORA_CONNECT="${USERNAME}/${PASSWORD}"
if [ "$SERVICE_NAME" != "" ] ; then
export ORACLE_SID=${SERVICE_NAME}
fi
fi
#
# Get the current terminal width to use as much as we can - set to 80
# by default
#
TERMCOLS=$(stty -a | sed 's/;//g' | grep columns | sed 's/[a-z= ]//g')
TERMCOLS=${TERMCOLS:-80}
INFOWIDTH=$(expr $TERMCOLS - 42)
echo "Database ${ORACLE_SID}"
(echo "set pagesize 0"
echo "set feedback off"
echo "set recsep off"
echo "set pause off"
echo "set echo off"
echo "set linesize ${TERMCOLS}"
echo "column info format A${INFOWIDTH} word_wrapped"
echo "column pid format A9"
echo "column uname format A10"
echo "select 'Process # Username %CPU %MEM Elapsed Info' || chr(10) ||"
echo " '--------- ---------- ---- ---- ---------- ' ||"
echo " lpad('-', ${INFOWIDTH},'-')"
echo 'from dual;'
for oraproc in $(ps -e -o 'pcpu pmem etime pid args' | grep ${ORACLE_SID} | grep -v grep | sort -rn | head | awk '{printf("%s|%s|%s|%s\n",$1,$2,$3,$4);}')
do
pid=$(echo $oraproc | cut -f4 -d'|')
cpu=$(echo $oraproc | cut -f1 -d'|')
mem=$(echo $oraproc | cut -f2 -d'|')
elapsed=$(echo $oraproc | cut -f3 -d'|')
echo "select '$pid' pid, substr(s.username,1,10) uname,lpad('$cpu',4),"
echo " lpad('$mem',4),lpad('$elapsed',10),"
echo ' substr(decode(s.module,NULL,s.program,'
echo " s.module||decode(s.action,NULL,'','/'||s.action)),1,30)"
echo " ||chr(10)||t.sql_text info"
echo 'from v$session s,'
echo ' v$sql t'
echo 'where s.paddr=(select addr from v$process'
echo " where spid='$pid')"
echo 'and s.sql_address=t.address(+)'
echo 'and s.sql_hash_value=t.hash_value(+);'
done
echo "exit&quot | sqlplus -s ${ORA_CONNECT}

********

-- The following query (to be run by a DBA) lists for currently
-- active transactions who is running them (username + program
-- info when available, complete with session id ready to be used
-- in some 'ALTER SYSTEM KILL SESSION' command), the text of the
-- associated SQL query, how many rollback segment blocks we have
-- used so far and for how long the transaction has been running,
-- and the name of the table(s) on which the running transaction
-- has put row-locks. Note that some innocent-looking UPDATE can
-- indeed put row-locks on many tables if there are triggers updating,
-- inserting or deleting other tables.
-- Transactions are ordered by duration (transactions which have
-- been running the longer first).
--
-- This is quite useful for monitoring long-running updates and
-- locking problems. Works on Oracle7 and Oracle8.
--
set recsep off
column "WHO" format A35
column "TEXT" format A40 word_wrapped
column "RB BLKS/S RUNNING" format A17
clear breaks
break on "WHO" on "ROLLBACK BLOCKS" on "RB BLKS/S RUNNING" on "TEXT" skip 1
select to_char(se.sid ) || ',' || to_char(se.serial#) || ' ' || se.username
|| '/' ||
decode(ltrim(se.module || ' ' || se.action || ' ' || se.client_info),
'', se.program,
ltrim(se.module || ' ' || se.action || ' ' || se.client_info))
"WHO",
s.sql_text "TEXT",
to_char(x.used_ublk )||'/'||
to_char((sysdate-to_date(x.start_time,'MM/DD/RR HH24:MI:SS'))*86400)
"RB BLKS/S RUNNING",
o.name "TABLE"
from v$sqlarea s,
v$session se,
sys.obj$ o,
v$locked_object l,
v$transaction x,
v$lock l2
where se.sql_address = s.address
and x.ses_addr = se.saddr
and o.obj# = l.object_id
and l.xidusn = x.xidusn
and l.xidslot = x.xidslot
and l.xidsqn = x.xidsqn
and l2.id2 = x.xidsqn
and l2.id1 = 65536 * x.xidusn + x.xidslot
and l2.type = 'TX'
order by (sysdate-to_date(x.start_time,'MM/DD/RR HH24:MI:SS'))*86400 desc,
1, 4

 

[GoSooJJ]

thank you and sorry about to post wrong place.