▷ Load a file into oracle db, 파일 DB 적재 스크립트

[ Load a file into oracle db ]

#!/bin/ksh

##################################################################
#                       Loading File into ORACLE Database
#========================================================
# Description   : Monitoring the file in specific directory,
#                 if there isn't any changes to the file then load the file into oracle database
# FileName      : file2dbload.sh
# Notes         :
# Modification  : 2009/09
# Modified by   : Jung, Jong-Pil
##################################################################

HOMEDIR="/export/home/jpjung";
WORKDIR="/current"
BACKUP="/backup"
LOG="/log"
CTL="/ctl"
SQL="/sql"

DBLOADER="$ORACLE_HOME/bin/sqlldr"
DBLISTER="$ORACLE_HOME/bin/sqlplus"

LOADFILE="/test.txt"

logDate=`date +%Y%m%d`;
LOGFILE=$HOMEDIR$LOG"/file2dbload_"$logDate".log";
touch $LOGFILE;

logTime=`date +"%Y-%m-%d %H:%M:%S"`" : ";
echo $logTime >> $LOGFILE
echo $logTime >> $LOGFILE
echo $logTime"-------------------------------------------------------" >> $LOGFILE
echo $logTime"--- FILES STATUS CHECK Shell Script Started !!!     ---" >> $LOGFILE
echo $logTime"-------------------------------------------------------" >> $LOGFILE
echo $logTime >> $LOGFILE
echo $logTime >> $LOGFILE

while [ 1 ]
do
logDate=`date +%Y%m%d`;
LOGFILE=$HOMEDIR$LOG"/file2dbload_"$logDate".log";
touch $LOGFILE;

logTime=`date +"%Y-%m-%d %H:%M:%S"`" : ";

if [ -s $HOMEDIR$WORKDIR$LOADFILE ]
then
echo $logTime"FILE EXIST in "$HOMEDIR$WORKDIR$LOADFILE >> $LOGFILE;

PRESIZE=0;
CURSIZE=`ls -al $HOMEDIR$WORKDIR$LOADFILE | tr -s "  " " " | cut -f 5 -d " "`

while [ $PRESIZE -ne $CURSIZE ]
do
logTime=`date +"%Y-%m-%d %H:%M:%S"`" : ";
echo $logTime"PRE FILE SIZE : "$PRESIZE" / CURRENT FILE SIZE : "$CURSIZE >> $LOGFILE
echo $logTime"Sleeping 1 Second..." >> $LOGFILE

PRESIZE=$CURSIZE
CURSIZE=`ls -al $HOMEDIR$WORKDIR$LOADFILE | tr -s "  " " " | cut -f 5 -d " "`
done

logTime=`date +"%Y-%m-%d %H:%M:%S"`" : ";
mv $HOMEDIR$WORKDIR$LOADFILE $HOMEDIR$LOADFILE

echo $logTime"Performance DB Loading Started !!!" >> $LOGFILE;

# PRE-Load Job
#$DBLISTER scott/tiger < $HOMEDIR$SQL/pre.sql ;

$DBLOADER userid=scott/tiger control=$HOMEDIR$CTL/test.ctl \
log=$HOMEDIR$LOG/test.log bad=$HOMEDIR$LOG/test.bad \
data=$HOMEDIR$LOADFILE

# POST-Load Job
#$DBLISTER scott/tiger < $HOMEDIR$SQL/post.sql ;

echo $logTime"Performance DB Loading Ended !!!" >> $LOGFILE;

backupTime=`date +"%Y%m%d%H%M%S"`;
mv $HOMEDIR$LOADFILE $HOMEDIR$BACKUP$LOADFILE"_"$backupTime;
# else
# echo $logTime" FILE NOT EXIST in FILE Directory !!!" >> $LOGFILE;
fi

logTime=`date +"%Y-%m-%d %H:%M:%S"`" : ";

# echo $logTime"Sleeping 10 Seconds..." >> $LOGFILE
# echo $logTime >> $LOGFILE
sleep 5;
done