Oracle of Nashville

Shell scripting can be an effective tool for monitoring Oracle databases because it allows you to automate tasks and perform checks on a regular basis. Here is a Bourne shell script that you can use to monitor Oracle database sessions and send an email alert to admin@domain.com when the number of sessions is greater than 100: #!/bin/sh # Query to count the number of Oracle database sessions SESSION_COUNT=`sqlplus -S << EOF SELECT COUNT(*) FROM v\\$session WHERE type = 'USER'; EOF` # Check if the number of sessions is greater than 100 if [ $SESSION_COUNT -gt 100 ] then # Send an email alert to admin@domain.com mail -s "Oracle Session Alert" admin@domain.com << EOF The number of Oracle database sessions is currently $SESSION_COUNT, which is greater than the threshold of 100. Please check the database and take necessary action. EOF fi This script uses the sqlplus command to execute a SQL query that counts the number of user sessions in ...

So, how can you find files in Unix older than a specified number of days? The solution is the find command with the option mtime. mtime is the modified time of a file. It's the last time the data in the file was changed. Simple enough. Find any file in /oback and all of its subdirectories that were last modified 1 day ago: find /oback/ -mtime +1 Find any file in /oback, excluding subdirectories , that were last modified 2 days ago: find /oback/* ! -name . -prune -type f -mtime 2

To pass an argument to a shell script from the command line, simply follow the shell command with your argument: $ myShell.ksh arg1 Unix makes using the arguments in your shell script just as easy with some pre-defined variables: $# - the number of arguments passed $* - all arguments passed $0 - the command run from the Unix prompt $1 - $9 - argument one through nine passed ot the script For instance, say I have the following shell script called myShell.ksh: echo "This is myShell.ksh" echo $# echo $* echo $0 echo $1 echo $2 Executing the script with the command $ myShell.ksh bob smith would produce the following output: This is myShell.ksh 2 bob smith myShell.ksh bob smith

Running SQL from a shell is really useful. Most of the time I use it to capture data for logs. For example, I have a shell cronned hourly that gets the number of sessions logged in and the size of the development tablespace. I implemented this during registration to monitor the growth of the database and average user sessions. This example queries for the number of sessions and places it in a variable called numsession : numsession="`sqlplus -s /nolog<<EOF connect / as sysdba set heading off; set echo off; set feedback off; select count(*) from v\\$session where username is not null; exit; EOF`"