whatsession (DBTier Script: executes using -- sqlplus / as sysdba)
What EBusiness Suite Database Sessions are ACTIVE/INACTIVE.
What Program are these Sessions Executing.
What Database Background Sessions (PMON, SMON, LGWR, DIAG are ACTIVE.
Example:
28-MAY-13 oratest OATC DBA Report Page: 1
16:23-51 Oracle Current Session(s)
whatsession
UNIX Oracle Oracle
User Process System Serial
OS User Name ID SID Number Program Status
-------- -------- -------- ------ ------ ----------------------- --------
applmgr APPS 30802110 9 11 PODAMGR@oatcapd04 (TNS ACTIVE
applmgr APPS 56557806 47 31 JDBC Thin Client ACTIVE
applmgr APPS 13042002 1860 16041 frmweb@oatcapd04 (TNS INACTIVE
wddunha RFREADON 43319404 811 2511 Toad.exe INACTIVE
oracle 37617776 1 1 oracle@oatcdbd03 (DIA0) ACTIVE
oracle 7143576 2 1 oracle@oatcdbd03 (LGWR) ACTIVE
oracle 47972450 3 11 oracle@oatcdbd03 (QMNC) ACTIVE
oracle 52560102 5 1 oracle@oatcdbd03 (Q003) ACTIVE
oracle 35127540 251 1 oracle@oatcdbd03 (MMAN) ACTIVE
oracle 7930134 252 1 oracle@oatcdbd03 (CKPT) ACTIVE
oracle 44302440 253 5 oracle@oatcdbd03 (CJQ0) ACTIVE
oracle 17498174 501 1 oracle@oatcdbd03 (PMON) ACTIVE
oracle 33292494 502 1 oracle@oatcdbd03 (DBW0) ACTIVE
oracle 20906014 503 1 oracle@oatcdbd03 (SMON) ACTIVE
oracle 47448084 751 1 oracle@oatcdbd03 (PSP0) ACTIVE
oracle 22347894 752 1 oracle@oatcdbd03 (DBW1) ACTIVE
oracle 45613172 753 1 oracle@oatcdbd03 (RECO) ACTIVE
oracle 25165992 848 5617 oracle@oatcdbd03 (W006) ACTIVE
oracle 53280808 1001 1 oracle@oatcdbd03 (VKTM) ACTIVE
oracle 19660874 1002 1 oracle@oatcdbd03 (DBW2) ACTIVE
oracle 47054928 1003 1 oracle@oatcdbd03 (MMON) ACTIVE
oracle 50987206 1056 2411 oracle@oatcdbd03 (J000) ACTIVE
oracle 41812024 1251 1 oracle@oatcdbd03 (GEN0) ACTIVE
oracle 16777320 1252 1 oracle@oatcdbd03 (DBW3) ACTIVE
oracle 51445764 1253 1 oracle@oatcdbd03 (MMNL) ACTIVE
oracle 48562184 1255 1 oracle@oatcdbd03 (Q000) ACTIVE
oracle 54001878 1338 21091 oracle@oatcdbd03 (J001) ACTIVE
oracle 38797368 1501 1 oracle@oatcdbd03 (DIAG) ACTIVE
oracle 16187630 1502 1 oracle@oatcdbd03 (DBW4) ACTIVE
oracle 38666334 1506 5 oracle@oatcdbd03 (SMCO) ACTIVE
oracle 48037902 1752 1 oracle@oatcdbd03 (DBRM) ACTIVE
oracle 10027188 1753 1 oracle@oatcdbd03 (DBW5) ACTIVE
oracle 42270778 1757 3 oracle@oatcdbd03 (M001) ACTIVE
oracle 39190606 1850 1415 oracle@oatcdbd03 (W002) ACTIVE
412 rows selected
#!/usr/bin/ksh
# whatsession ()
#------------------------------------------------------------------------#
# Module Name: whatsession (c) 2012 #
# #
# Purpose: Report on the v$SESSION. #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ -------------------------------------- #
# 10-May-2012 M.Barone Module design/creation #
#------------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
clear
#----------------------------------------------------------------#
# TEST: Number ($#) of arguments passed to this function? #
#----------------------------------------------------------------#
if [ $# = 0 ]
then
clear
echo
echo " #---------------------------------------------------#"
echo " # whatsession: Oracle Session Information #"
echo " #---------------------------------------------------#"
echo
echo " Connecting as SYSDBA"
sleep 2
clear
else
SEARCH=$1
clear
fi
ORANAM=${ORASID}
#-----------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#-----------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT / as SYSDBA
clear BREAKS
clear BUFFER
clear COLUMNS
clear COMPUTES
clear SCREEN
clear SQL
clear TIMING
set feedback on
set flush on
set heading on
set pause off
set space 1
set termout on
set verify on
set linesize 80
set pagesize 999
SPOOL /tmp/whatsession_$$.txt
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN osuser FORMAT A08 Heading 'OS User'
COLUMN username FORMAT A08 Heading 'User|Name'
COLUMN spid FORMAT A8 Heading 'UNIX|Process|ID'
COLUMN sid FORMAT 99999 Heading 'Oracle|System|SID'
COLUMN serial# FORMAT 99999 Heading 'Oracle|Serial|Number'
COLUMN program FORMAT A25 Heading 'Program' WORD_WRAPPED
COLUMN status FORMAT A08 Heading 'Status'
TTITLE LEFT DATEVAR -
RIGHT 'Page:' FORMAT 999 SQL.pno -
CENTER '$(uname -n) ${ORACLE_SID} DBA Report' skip 1 -
LEFT TIMEVAR -
CENTER 'Oracle Current Session(s)' skip 1 -
CENTER 'whatsession' skip 2
SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
TO_CHAR(sysdate, 'HH24:MI-SS') timevalue,
SES.osuser,
SES.username,
PRO.spid,
SES.sid,
SES.serial#,
SES.program,
SES.status
FROM sys.V\$SESSION SES,
sys.V\$PROCESS PRO
WHERE SES.PADDR = PRO.ADDR (+)
ORDER BY SES.osuser, SES.username, SES.sid;
EXIT;
END_FILE
print
print "Please see /tmp/whatsession_$$.txt"
print
}
No comments:
Post a Comment