Oracle Database

#!/bin/ksh
############################################################################
#
#       Script :        trace_sess.ksh
#
#       Date :          14th March 2010
#
#       Author :        Mark Ramsay
#
#       Description :   This script will map an OS process
#                       to a SQL sid and serial and turn on
#                       tracing using DBMS_MONITOR.SESSION_TRACE_ENABLE.
#                       Using the disable parameter the script will also
#                       disable a trace using
#                       DBMS_MONITOR.SESSION_TRACE_DISABLE.
#
#       Parameters :    $1 - UNIX process id. (Mandatory)
#                       $2 - disable or DISABLE (Optional)
#
#       Notes:          In order to use this script a file called locked
#                       needs to be set-up that contains the encrypted
#                       database SYSTEM password.
#                       To set-up the locked file follow these steps
#
#                       1. Create a temporary file called unlocked
#                          containing the SYSTEM password.
#                       2. Run the following command
#
#                          crypt key <unlocked >locked
#
#                          where key=encryption key.
#
#                       3. Delete the unlocked file.
#
#                       4. Set permissions on file locked to 400.
#
#                       5. Update line 96 of this script with the
#                          encryption key.
#
#                       The script works using a hardcoded SID.
#                       Update the SID on line 90.
#
#       DISCLAIMER:     Individuals use this script at their own risk.
#                       It is entirely the users responsibility to
#                       determine if the level of security contained
#                       within this script meets their needs and is
#                       suitable for use.
#
#
# History       Date      Name      Reason
#               ----      ----      ------
#               dd/mm/yy  zzz       xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
#
############################################################################
#
# Ensure mandatory paramters are set
#
############################################################################
clear

if [[ $1 = "" ]]
then echo "\$1 is mandatory."
     echo "\$1 = OS process to be SQL traced."
     echo "\$2 = disable (Optional). Set \$2 to stop the tracing for a given OS porcess."
     echo "Script exiting..."
     exit
fi

############################################################################
#
# Check $2 is set to a valid parameter if set.
#
############################################################################

if [[ $2 = "" ]] || [[ $2 = "DISABLE" ]] || [[ $2 = "disable" ]]
then :
else echo "Optional parameter \$2 not set to a correct value."
     echo "\$2 must be disable or DISABLE or not set."
     echo "You have set it to $2."
     echo "Script exiting..."
     exit
fi

############################################################################
#
# Set-up and validate variables
#
############################################################################

ORACLE_SID="MYSID"
ORAENV_ASK=NO
. oraenv

SDS_process=$1
SDS_disable=$2
SDS_ora_pw=`crypt mykey <locked`
SDS_session_view=v\$session

SDS_serial_id=`sqlplus -S system/$SDS_ora_pw <<EOF
set pages 0
set hea off
set feedback off
select trim(SERIAL#) from $SDS_session_view where PROCESS=$SDS_process
/
EOF`

SDS_sid=`sqlplus -S system/$SDS_ora_pw <<EOF
set pages 0
set hea off
set feedback off
select trim(sid) from $SDS_session_view where PROCESS=$SDS_process
/
EOF`

############################################################################
#
# Strip off CR's
#
############################################################################

SDS_sid=`echo $SDS_sid|sed -e 's/ //'`
SDS_serial_id=`echo $SDS_serial_id|sed -e 's/ //'`

############################################################################
#
# Check \$2 parameter
#
############################################################################

if [[ $SDS_disable = "disable" ]] || [[ $SDS_disable = "DISABLE" ]]
then echo ""
     echo "********************************"
     echo "* Turning tracing off for $SDS_process *"
     echo "********************************"
     echo ""
     echo "Do you want to continue? (y/n)"
     read answer
     if [ $answer != "Y" ] && [ $answer != "y" ]
     then echo "User answered: " $answer
          echo "Aborted by User."
          echo "Script exiting.."
          exit
     fi
     sqlplus system/$SDS_ora_pw <<EOF
exec DBMS_MONITOR.SESSION_TRACE_DISABLE($SDS_sid,$SDS_serial_id)
EOF
     echo ""
     echo "Trace Disable"
     echo ""
     echo "PRESS RETURN TO CONTINUE"
     read pause_some_time
     sqlplus system/$SDS_ora_pw <<EOF
col username for a10
col module for a15
col sql_trace for a9
col sql_trace_waits for a15
col sql_trace_binds for a15
select username,module,sid,sql_trace,sql_trace_waits,sql_trace_binds from $SDS_session_view where process=$SDS_process;
EOF
     exit
else echo "Starting Tracing..."
fi

############################################################################
#
# Start Tracing
#
############################################################################

sqlplus -S system/$SDS_ora_pw <<EOF
set hea off
select 'You are about to trace Username: '|| USERNAME from $SDS_session_view where PROCESS=$SDS_process
/
select 'Username ' || USERNAME || ' is running module '||MODULE from $SDS_session_view where PROCESS=$SDS_process
/
EOF

echo "SERIAL#: "$SDS_serial_id
echo "SID: "$SDS_sid
echo ""

echo "Do you want to continue? (y/n)"
read answer

if [ $answer != "Y" ] && [ $answer != "y" ]
then echo "User answered: " $answer
     echo "Aborted by User."
     echo "Script exiting.."
     exit
fi

echo "Starting to trace user..."

sqlplus system/$SDS_ora_pw <<EOF
exec DBMS_MONITOR.SESSION_TRACE_ENABLE($SDS_sid,$SDS_serial_id,TRUE,TRUE)
EOF

echo ""
echo "Trace Enabled"
echo ""
echo "PRESS RETURN TO CONTINUE"
read pause_some_time

sqlplus system/$SDS_ora_pw <<EOF
col username for a10
col module for a15
col sql_trace for a9
col sql_trace_waits for a20
col sql_trace_binds for a20
select username,module,sid,sql_trace,sql_trace_waits,sql_trace_binds from $SDS_session_view where process=$SDS_process;
EOF

############################################################################
#
# End Script.
#
############################################################################

echo "JOB COMPLETE"
exit 0