直接给出数据库连接字串和列有表名称的文本文件，即可进行exp/imp
# begin of exptable
#!/bin/ksh
PARALLEL_LIMIT=5
WAIT_TIME=10
PARAMETERS="DIRECT=Y COMPRESS=N ROWS=Y INDEXES=Y STATISTICS=NONE";export PARAMETERS
TARGET_PATH=./done
LOGFILE_PATH=./elogs
USER_ID=$1
TABLELIST_FILE=$2
EXTEND_CODE=$3
exptabledata()
{
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 开始导出数据表"$2" ..."
echo "Command Line: "exp USERID=******** $PARAMETERS TABLES=$2 FILE=$3 LOG=$4
ZIP_FILE=$3.gz
rm -f $3
#exptabledata $USER_ID $TABLE_NAME_UPPER $DMP_FILE $LOG_FILE $TARGET_PATH
exp USERID=$1 $PARAMETERS TABLES=$2 FILE=$3 LOG=$4
#echo `date +"%Y-%m-%d %H:%M:%S"`" -- 压缩数据文件"$3" ..."
#if [ -s $3 ];then gzip -f $3 ; fi
if [ -s $ZIP_FILE ];then
mv -f $ZIP_FILE $5/$ZIP_FILE
else
mv -f $3 $5/$3
fi
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 表"$2"数据导出完成 !!!
"
}
#校验用户名/导出表清单文件是否存在
if [ "NULL$USER_ID" = "NULL" -o "NULL$TABLELIST_FILE" = "NULL" ]
then
echo "
"
echo "Usage: $0 UserID/Paasword[@ServiceName] tables.lst [Extend]"
echo "UserID/Paasword[@ServiceName] -- 数据库链接字串"
echo "tables.lst -- 导出表清单文件"
echo "Extend -- 扩展名"
echo "
"
exit;
fi;
#校验导出表清单文件
if [ ! -r $TABLELIST_FILE ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $TABLELIST_FILE" -- 导出表清单文件读取无效 !!!"
echo "
"
exit
fi
#校验目标目录
if [ -d $TARGET_PATH ]
then
if [ ! -w $TARGET_PATH ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $TARGET_PATH" -- 目标目录写入无权限 !!!"
echo "
"
exit
fi
else
mkdir -p $TARGET_PATH
if [ ! $? ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $TARGET_PATH" -- 目标目录创建失败 !!!"
echo "
"
exit
fi
fi
#校验LOG目录
if [ -d $LOGFILE_PATH ]
then
if [ ! -w $LOGFILE_PATH ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $LOGFILE_PATH" -- LOG目录写入无权限 !!!"
echo "
"
exit
fi
else
mkdir -p $LOGFILE_PATH
if [ ! $? ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $LOGFILE_PATH" -- LOG目录创建失败 !!!"
echo "
"
exit
fi
fi
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 数据导出开始 ..."
echo "目标目录 -- "$TARGET_PATH
echo "表清单文件 -- "$TABLELIST_FILE
echo "
"
#读取表名称进行数据导出
while read LARGE_TABLE
do
# 根据进程数休眠
read PARALLEL_LIMIT < parallel.limit
while [ `ps -ef|grep "exp USERID"|grep -v grep|wc -l` -ge $PARALLEL_LIMIT ]
do
sleep $WAIT_TIME
read PARALLEL_LIMIT < parallel.limit
done
DFILE_NAME=`echo $LARGE_TABLE|awk -F '|' '{print $1;}'|tr '[:upper:]' '[:lower:]'`
TABLE_NAME=`echo $LARGE_TABLE|awk -F '|' '{print $2;}'|tr '[:lower:]' '[:upper:]'`
if [ "NULL$DFILE_NAME" = "NULL" -a "NULL$TABLE_NAME" = "NULL" ]
then
continue
else
if [ "NULL$TABLE_NAME" = "NULL" ];then TABLE_NAME=`echo $DFILE_NAME|tr '[:lower:]' '[:upper:]'`;fi
fi
if [ "NULL$EXTEND_CODE" = "NULL" ]
then
DMP_FILE=$DFILE_NAME".dmp"
LOG_FILE=$LOGFILE_PATH/$DFILE_NAME".log"
else
DMP_FILE=$DFILE_NAME"_"$EXTEND_CODE".dmp"
LOG_FILE=$LOGFILE_PATH/$DFILE_NAME"_"$EXTEND_CODE.log
fi
exptabledata $USER_ID $TABLE_NAME $DMP_FILE $LOG_FILE $TARGET_PATH &
done < $TABLELIST_FILE
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 数据导出结束 !!!"
echo "目标目录 -- "$TARGET_PATH
echo "表清单文件 -- "$TABLELIST_FILE
echo "
"
# end of exptable
# begin of imptable
#!/bin/ksh
PARALLEL_LIMIT=10
WAIT_TIME=10
PARAMETERS="BUFFER=104857600 IGNORE=Y COMMIT=Y FULL=Y";export PARAMETERS
LOGFILE_PATH=./ilogs
INDEX_PATH=./index
BACKUP_PATH=./bak
DOING_PATH=./doing
USER_ID=$1
DATAFILE_PATH=$2
imptabledata()
{
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 开始导入数据文件"$2" ..."
echo "Command Line: imp USERID=********" $PARAMETERS INDEXES=N FILE=$3 LOG=$5
#imptabledata $USER_ID $DMP_FILE $INDEX_FILE $LOG_FILE
#imp USERID=$1 $PARAMETERS INDEXES=Y FILE=$2 INDEXFILE=$3
imp USERID=$1 $PARAMETERS INDEXES=N FILE=$2 LOG=$4
mv -f $2 $5
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 文件"$2"导入完成 !!!
"
}
#校验用户名/导入表清单文件是否存在
if [ "NULL$USER_ID" = "NULL" ]
then
echo "
"
echo "Usage:
$0 UserID/Paasword[@ServiceName]"
echo "UserID/Paasword[@ServiceName] -- 数据库链接字串"
echo "
"
exit;
fi;
#校验数据文件存储目录
if [ ! ( -d $DATAFILE_PATH -a -r $DATAFILE_PATH ) ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $DATAFILE_PATH" -- 目标目录无读取权限 !!!"
echo "
"
exit
fi
#校验LOG目录
if [ -d $LOGFILE_PATH ]
then
if [ ! -w $LOGFILE_PATH ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $LOGFILE_PATH" -- LOG目录无写入权限 !!!"
echo "
"
exit
fi
else
mkdir -p $LOGFILE_PATH
if [ ! $? ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $LOGFILE_PATH" -- LOG目录创建失败 !!!"
echo "
"
exit
fi
fi
#校验INDEX目录
if [ -d $INDEX_PATH ]
then
if [ ! -w $INDEX_PATH ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $INDEX_PATH" -- INDEX目录无写入权限 !!!"
echo "
"
exit
fi
else
mkdir -p $INDEX_PATH
if [ ! $? ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $INDEX_PATH" -- INDEX目录创建失败 !!!"
echo "
"
exit
fi
fi
#校验BACKUP目录
if [ -d $BACKUP_PATH ]
then
if [ ! -w $BACKUP_PATH ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $BACKUP_PATH" -- BACKUP目录无写入权限 !!!"
echo "
"
exit
fi
else
mkdir -p $BACKUP_PATH
if [ ! $? ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $BACKUP_PATH" -- BACKUP目录创建失败 !!!"
echo "
"
exit
fi
fi
#校验DOING目录
if [ -d $DOING_PATH ]
then
if [ ! -w $DOING_PATH ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $DOING_PATH" -- DOING目录无写入权限 !!!"
echo "
"
exit
fi
else
mkdir -p $DOING_PATH
if [ ! $? ]
then
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $DOING_PATH" -- DOING目录创建失败 !!!"
echo "
"
exit
fi
fi
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 开始导入数据文件目录"$DATAFILE_PATH" ..."
echo "
"
#读取表名称进行数据导入
if [ ! -x imptables.stop ];then echo "running" > imptables.stop;fi
read IMP_STOP < imptables.stop
while [ $IMP_STOP != "stop" ]
do
ls -p $DATAFILE_PATH|grep ".dmp" > dmpfile.lst
while read DMP_FILE
do
# 根据进程数休眠
read PARALLEL_LIMIT < parallel.limit
while [ `ps -ef|grep "imp USERID"|grep -v "grep"|wc -l` -ge $PARALLEL_LIMIT ] ;
do
sleep $WAIT_TIME
read PARALLEL_LIMIT < parallel.limit
done
TABLE_NAME=${DMP_FILE%.*}
LOG_FILE=$TABLE_NAME".log"
INDEX_FILE=$TABLE_NAME".sql"
if [ "NULL$DMP_FILE" = "NULL" ]
then
continue
fi
mv -f $DATAFILE_PATH/$DMP_FILE $DOING_PATH/$DMP_FILE
if [ -r $DOING_PATH/$DMP_FILE ]
then
imptabledata $USER_ID $DOING_PATH/$DMP_FILE $INDEX_PATH/$INDEX_FILE $LOGFILE_PATH/$LOG_FILE $BACKUP_PATH/$DMP_FILE &
else
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- 错误 !!!"
echo $DMP_FILE" -- 数据文件读取失败 !!!"
echo "
"
fi
done < dmpfile.lst
if [ `wc dmpfile.lst|awk '{print $1}'` -eq 0 ];then sleep 120; fi
ls -p $DATAFILE_PATH|grep ".dmp" > dmpfile.lst
read IMP_STOP < imptables.stop
done
echo "
"
echo `date +"%Y-%m-%d %H:%M:%S"`" -- imptables.stop时数据导入程序退出 !!!"
echo "
"
# end of imptable