Trace-SQL-Session

Tracing SQL Statements for 10g

Oracle Database » Script Library » Tracing SQL Statements for 10g

#!/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 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