Author: Steve Callan

Introduction:

In this article I will discuss some of my personal favorite tips and commands while working with oracle on a UNIX environment.

Initializing scripts:

Any script that rely on access to SQL*Plus or SQL*LDR on the same machine can easily be instantiated. Consider the below code snippet and put it into its own file. Name the file as shell.txt and save it in the user's $HOME directory ($HOME refers to /opt/oracle).

#! /usr/bin/ksh
#<----------------------------------------------------------->
# Check Oracle environment
#<----------------------------------------------------------->  
if [ -z "${ORACLE_HOME}" ]
then
echo ""
echo "ORACLE_HOME is not set."
echo "ORACLE_HOME is needed for SQL*Plus and SQL*LDR."
echo ""
echo "Exiting $0"
echo ""
exit 1
fi  
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo ""
echo "Can not find sqlplus."
echo ""
echo "Exiting $0"
echo ""
exit 1
fi

Environment checking and other executables:  

>When starting a new script, you have environment checking already added in, you also have the new script file created in the current working directory. Below example check for ORACLE_HOME and the sqlplus executable. Other executables can be included/checked, but there is a likelihood that if sqlplus exists under ORACLE_HOME, so does sqlldr.

linux.box.net > cat ~/shell.txt > my_new_script.ksh
linux.box.net > more my_new_script.ksh
#! /usr/bin/ksh
#<----------------------------------------------------------->
# Check Oracle environment
#<----------------------------------------------------------->  
if [ -z "${ORACLE_HOME}" ]
then
echo ""
echo "ORACLE_HOME is not set."
echo "ORACLE_HOME is needed for SQL*Plus and SQL*LDR."
echo ""
echo "Exiting $0"
echo ""
exit 1
fi  
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo ""
echo "Can not find sqlplus."
echo ""
echo "Exiting $0"
echo ""
exit 1
fi  

Testing and branching:  

When you add in a "yes or no" block. The script can then test SOME_VARIABLE's value and branch appropriately.

#<----------------------------------------------------------->
# Variable assignment
#<----------------------------------------------------------->
YN_ANSWER=""
echo "Are you doing <fill in the blank> (y/n) [Y]: "
read YN_ANSWER  
while [ "$YN_ANSWER" != "Y" -o "$YN_ANSWER" != "N" ]
do
case "$YN_ANSWER"
in
y | Y | yes | YES | Yes | "" ) YN_ANSWER="Y" break;;
n | N | no | NO | No ) YN_ANSWER="N" break;;
* )
echo ""
echo "Please enter y or n (Ctrl-c to quit): "
read YN_ANSWER;;
esac;
done  
if [ "${YN_ANSWER}" = "Y" ]
then
SOME_VARIABLE=Y
else
SOME_VARIABLE=Y
fi

Date formatting/masking in UNIX:  

Using date formatting/masking in UNIX does everything UNIX does and more because of the several ways in which dates can be formatted. You can also include a random number (concatenated with the date query) if the 86,400 seconds in a day in combination with the date is not enough to make a file name unique. Two things to take note of are to include the single quotation mark and the use of the translate command.

"`" Character:

The "`" character is hard to see, especially if your editor allows color mode, so don't forget to include it (or check for it during troubleshooting).

Translate command:

The translate (tr) command assist to stop carriage/line return when referencing the variable. If your output seem like what is shown below, the tr command's conditioning of the variable's value will make the variable more "normal."

Shell Script Variable:

At most anything from a SQL*Plus query can be returned into a shell variable. Although there are other ways of adding a timestamp as part of a file's name, this example uses the date returned from a query and places it into a file's name (e.g., a spool file).

TS=`sqlplus -s username/password@SID <<EOF
set heading off feedback off verify off
select to_char(sysdate, 'YYYYMMDD_SSSSS') from dual;
exit
EOF `
TS=`
echo $TS | tr "[a-z]" "[A-Z]"`
echo "The timestamp is $TS"

As a test, put this into my_new_script.ksh and run it.

linux.box.net> my_new_script.ksh
The timestamp is 20060720_18200  

Alert log checker:

You can create an alert log checker and have it send mail when it discovers problems or alertable conditions in the end of the alert log. Add a cron job to check every five minutes (as an example). Use the tail command to output the bottom however many lines you want from the alert log into a temp file, grep for ORA-xxxxx or whatever else is of interest, and if you get a hit, send the results or temp file via email.

This method isn't bulletproof in that a lot can happen in five minutes and the scan (using tail -#_of_lines) may miss the event you were hoping to be alerted on. Not to knock the improvements in OEM/Database Control/whatever, but a cron on UNIX is quite a bit more trustworthy than relying on an Oracle agent. Keep in mind that this isn't an either-or choice. The cron can backup the agent, and vice versa.

Mail Function:

Let's say you just spooled the output of a query into a file. If you work in a PC desktop/UNIX server environment, as is very common, how do you get the spool file to your desktop (or other users, for that matter)? Are you one of those who copies the file to your home directory, starts up PuTTY/WinSCP3/Tectia/etc., copies the file onto your PC, then attached it to an email? If the file is small enough (relatively speaking), send it via mail using:

The exact name of "mail" can vary across platforms/vendors, but either mail or mailx typically does the trick. Of course, your environment has to be configured to let mail to be sent from UNIX-land to PC-world.

Consistency:

There are a number of available shells like Bash, Korn, Bourne, C, and the list goes on and on. Each shell has it own nuances, so instead of having your scripts spread over several shells, pick one and stick with it. The payoff is consistency.

Switching between shells:

You can almost always switch between shells, so if your personal preference is the C-shell (hard to beat the tab key for file name completion), but all "real" work is done the Korn shell, learn how to switch between them.

‘at' command:

Related to cron jobs is the "at" command. On Linux, running a script is as simple as:

This is much easier and safer to perform when compared to waiting for the scheduled cron job to start, editing the crontab and introducing errors/typos, and preventing output from scrolling in your window. The "now" part can include noon , midnight , 0600, and a variety of other named time formats.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Automated Database management makes life easy!!
   Oracle Asynchronous COMMIT: Facts and Concerns!
   Common Database design mistakes – Do you avoid them?
   Reveal the shades of Oracle result_cache!!