1.查看定时任务
2.脚本-目录结构
1)config.ini
2)run.sh
3.命令解读
1)ls -1 路径文件夹
含义:ls -1 /home/oracle/shell/config/ 将文件夹config内的文件全部列出
[oracle@neptune config]$ ls -1 /home/oracle/shell/config/ >/home/oracle/shell/config/1.txt
2) sh x.sh 含义:调用脚本
sh $workPath/proc.sh $tbl_userid 'GET_TRUNCATE_ALL_TABLES' "$year-$month"
含义:调用过程,清空表
相关脚本内容: proc.sh
ls -1 $dataPath>$orgnoFile
把机构清单放入文件
3) for xx in .. do done 含义:循环执行
for orgno in `cat $orgnoFile`
do
sh $workPath/decrypt.sh $year $month $orgno
sh $workPath/sqlldr.sh $year $month $orgno
done
含义:循环机构清单中的行,按年月 机构 执行 decrypt.sh、sqlldr.sh
相关脚本:decrypt.sh、sqlldr.sh
4)decrypt.sh
filePath="$dataPath/$orgno/$year/$month/"
cd $filePath
unzip -o ${year}${month}.zip含义:在up_data文件夹的机构目录/年/月/解压(直接覆盖)
url=${decryptUrl}=$filePath
curl $url
含义:调用http请求服务
decryptUrl=http://xxx:30050/fileCommon/fileDecAndZip?filePath
for table in `cat $tablesFile`
doecho $table
if [ ${table:0:1} = '#' ]; then
continue
ficommCtrl=$workPath/$table.ctrl
echo 'LOAD DATA'> $commCtrl
echo 'CHARACTERSET UTF8'>> $commCtrl
filePath="$dataPath/$orgno/$year/$month/$table.dat"
fileAll="$dataPath/$orgno/$year/$month/$table.*"
bakPath="$workPath/$orgno/$year/$month/"
mkdir -p $bakPath
cnt=0
num=`sh $workPath/func.sh $tbl_userid 'pg_get_table_rownum.get_table_rownum' $table`
#echo 33$num
tableRow=${num#*.}含义:调用tbl的过程,得到tbl表的行数
filePath 为:$dataPath/$orgno/$year/$month/$table.dat
if [ -e "$filePath" ]; then
cnt=`cat $filePath | wc -l` 说明:.dat 中有多少行数据
echo $cnt
echo "INFILE \"$filePath\" ">>$commCtrl
echo ${type}
echo -e "append INTO TABLE $table \n Fields terminated by \"\" ">>$commCtrl
echo 'trailing nullcols' >>$commCtrl以上,将信息打印到commCtrl = shell下的.ctl文件中
cat $tableColPath/$table.ctrl>>$commCtrl
badFile="$workPath/$orgno/${table}_${year}${month}.bad"
logFile="$workPath/$orgno/${table}_${year}${month}.log"
说明:tableColPath=/home/oracle/shell/tables
echo "sqlldr userid=$tbl_userid control=$commCtrl rows=50000 bindsize=104857600 parallel=true log=$workPath/$table.log errors=999999999"
sqlldr userid=$tbl_userid control=$commCtrl rows=50000 bindsize=104857600 parallel=true bad=$badFile log=$logFile errors=999999999说明:sqlldr方式 加载入库tbl, rows 默认64行 每次提交的记录数
sqlldr user/password control=test.ctl skip=1 load=200000 errors=100 rows=1000 bindsize=33554432
user/password //数据库的用户名密码
control //sqlldr控制文件位置
skip=1 //表示跳过第一行,从第二行开始导入
load=200000 //表示并不导入所有的数据,只导入跳过skip参数后的200000条数据
rows=1000 //表示一次加载的行数,默认值为64,此处设置为1000
errors=100 //表示出错100次后,停止加载
bindsize=33554432 //表示每次提交记录缓冲区的大小,默认256k
bad文件:
log文件:
5)脚本调用 oracle存储过程
echo "sh $workPath/proc.sh "$tbl_userid" GET_SQLLDR_LOG "\'$year-$month\',\'$orgno\',\'$table\',$cnt""
sh $workPath/proc.sh "$tbl_userid" GET_SQLLDR_LOG "'$year-$month','$orgno','$table',$cnt,$tableRow,'$logFile','$badFile'"
proc.sh :
get_sqlldr_log存储过程:
6)移动到备份位置
mv $fileAll $bakPath
date
fidone
含义:从 /home/data/up_data/机构/$year/$month/$table.*
移动 到 /home/oracle/shell/机构/$year/$month/
tables.config:
$tablesFile:/home/oracle/shell/config/tables.config
func.sh
7) 存储过程查询表行数 pg_get_table_rownum
pg_get_table_rownum.get_table_rownum 表行数:
CREATE OR REPLACE PACKAGE BODY pg_get_table_rownum is
function get_table_rownum(pTableName in varchar2) return varchar2 is
vResult varchar2(1000);
vSql varchar2(1000);
BEGIN
vSql := 'select count(1) from ' || pTableName;
execute immediate vSql
into vResult;
return vResult;
end;
end pg_get_table_rownum;
8) 调用过程
run.sh 最后一句:
调过程跑批: