#!/bin/ksh # Commands typeset -u Command for Name in mkdir do Command=$Name case $Name in grep) PathNames=/usr/xpg4/bin:/usr/bin:/bin ;; *) PathNames=/usr/bin:/bin ;; esac while [[ -n $PathNames ]] do PathName=${PathNames%%:*} PathNames=${PathNames#*:} if [[ $PathName = $PathNames ]] then PathNames="" fi if [[ -x $PathName/$Name ]] then eval "$Command=$PathName/$Name" break fi done if [[ -z $(eval print `print \\$${Command}`) ]] then print -u2 "Unable to find a $Name executable" exit 1 fi done # Directories BaseDir=/home/oracle SQLDir=${BaseDir}/sql LogDir=${BaseDir}/logs ENVFileDir=/usr/local/bin # Misc ProgName=${0##*/} # Files SQLFile=${SQLDir}/${ProgName%%.sh}.sql # Parameters ORACLE_SID=$1 if [[ -z $ORACLE_SID ]] then print -u2 "ERROR - Oracle SID must be set or passed in" exit 1 fi # Get correct environment . ${ENVFileDir}/${ORACLE_SID}_env # Set the output directory OutFile=$LogDir/${ProgName%%.sh}_$ORACLE_SID.out # Run the SQL if [[ ! -d $BaseDir ]] then print -u2 "ERROR - Unable to find directory $BaseDir" exit 1 fi if [[ ! -d $LogDir ]] then $MKDIR $LogDir if [[ $? -ne 0 ]] then print -u2 "ERROR - Unable to create directory $LogDir" exit 1 fi fi if [[ ! -f $SQLFile ]] then print -u2 "ERROR - Unable to find file $SQLFile" exit 1 fi cd $LogDir if [[ $PWD != $LogDir ]] then print -u2 "ERROR - Unable to change directory to $LogDir" exit 1 fi $ORACLE_HOME/bin/sqlplus / as sysdba @$SQLFile $ORACLE_SID > $OutFile 2>&1 if [[ $? -ne 0 ]] then print -u2 "ERROR - SQLPLus errored - please investigate" exit 1 fi exit 0
You name the script with the same name as the SQL script you wish to run.
For example if you have a script called say
purge_stats.sqlthen name the script
purge_stats.sh
Here's an example of a simple SQL script
set trims on whenever sqlerror exit failure rollback spool purge_stats_&1..log set timing on exec dbms_stats.purge_stats(sysdate-14) spool off exit
Some usage notes
1. Environment file for the database must be of the format
<oracle_sid>_env
No comments:
Post a Comment