#!/bin/bash
file_name="/home/oracle/test/verfy"
declare loader_file_name
declare table_name
declare -i x=0
if [ ! -f "$file_name" ];
then
echo "file is loadding,please wait a minute! \n"
while [ ! -f $file_name ];
do
if [ "$x" == "4" ]; then
echo "Have wait 20 minutes,Wait Overtime! \n"
sleep 1s
exit
fi
sleep 5m
done
fi
# imp xiaocai/xiaocai file=/data/xiaocai_`date +"%Y-%m-%d"`.dmp fromuser=xiaocai touser=caixiao tables=(TEST1) ignore=y;
echo -e "please input the file date you want to load (eg:2010-01-03): \n"
read loader_file_date
echo -e "\n please input the table name you want to load: \n"
read table_name
if [ $loader_file_date == ~ ]
then
echo "you input a blank to loader_file_date\n"
exit
fi
if [ $table_name == ~ ]
then
echo "you input a blank to table_name\n"
exit
fi
UserId=""
PassWd=""
echo -e "\n please load the data to who(the username.) \n"
read UserId
echo -e "\n please input the user's password\n"
read PassWd
DATAPATH=`expr /home/oracle/test`
#SQL*LOADER
TAB_NAME=`expr ${table_name}`
IN_FILE=`expr ${DATAPATH}``expr /datafile_${loader_file_date}.dat`
CTRL_FILE=`expr ${DATAPATH}``expr /control_loader.ctl`
LOG_FILE=`expr ${DATAPATH}``expr /loader.log`
BAD_FILE=`expr ${DATAPATH}``expr /loader.bad`
if [ -f ${CTRL_FILE} ]
then
rm -f ${CTRL_FILE}
else
echo "${CTRL_FILE} not exist"
fi
if [ -f ${LOG_FILE} ]
then
rm -f ${LOG_FILE}
else
echo "${LOG_FILE} not exist"
fi
if [ -f ${BAD_FILE} ]
then
rm -f ${BAD_FILE}
else
echo "${BAD_FILE} not exist"
fi
#make control file
make_ctrl()
{
printf "LOAD DATA\n">>${CTRL_FILE};
printf "INFILE '${IN_FILE}'\n">>${CTRL_FILE};
printf "INTO TABLE ${TAB_NAME}\n">>${CTRL_FILE};
printf "TRUNCATE\n">>${CTRL_FILE};
printf "FIELDS TERMINATED BY ','\n">>${CTRL_FILE};
printf "(\n">>${CTRL_FILE};
printf "OWNER,\n">>${CTRL_FILE};
printf "OBJECT_NAME,\n">>${CTRL_FILE};
printf "SUBOBJECT_NAME,\n">>${CTRL_FILE};
printf "OBJECT_ID,\n">>${CTRL_FILE};
printf "DATA_OBJECT_ID,\n">>${CTRL_FILE};
printf "OBJECT_TYPE,\n">>${CTRL_FILE};
printf "CREATED DATE \"yyyy-mm-dd hh24:mi:ss\",\n">>${CTRL_FILE};
printf "LAST_DDL_TIME DATE \"yyyy-mm-dd hh24:mi:ss\",\n">>${CTRL_FILE};
printf "TIMESTAMP,\n">>${CTRL_FILE};
printf "STATUS,\n">>${CTRL_FILE};
printf "TEMPORARY,\n">>${CTRL_FILE};
printf "GENERATED,\n">>${CTRL_FILE};
printf "SECONDARY\n">>${CTRL_FILE};
printf ")\n">>${CTRL_FILE};
}
cd ${DATAPATH}
if [ -f ${IN_FILE} ]
then
make_ctrl
sqlldr $UserId/$PassWd control=$CTRL_FILE log=$LOG_FILE bad=$BAD_FILE rows=100000
else
echo "${IN_FILE} not exist"
exit 1
fi
if [ $? != 0 ]
then
echo "sql*loader have some errors! \n"
exit 1
fi
sqlplus "caixiao/caixiaocai"<
truncate table temp;
alter table test1 exchange partition p_3000 with table temp;
alter table test2 exchange partition p_3000 with table temp;
truncate table temp;
alter table test1 exchange partition p_6000 with table temp;
alter table test2 exchange partition p_6000 with table temp;
truncate table temp;
alter table test1 exchange partition p_max with table temp;
alter table test2 exchange partition p_max with table temp;
truncate table test1;
truncate table temp;
exit
阅读(1377) | 评论(0) | 转发(0) |
