和以前自动ftp,load data的那个shell相比,没有多大长进。只有两点新东西
[@more@]1)从数据库检索数据,赋值给shell变量(注意 -s 参数)
LOG_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<
SET FEED OFF
SET TERM OFF
SET HEAD OFF
SELECT count(*)
FROM refresh_log
WHERE user_name='$USERNAME'
AND obj_name='$TABLE_NAME';
EOF`
2)截掉字符串的最后一串。比如将 eden_flat_ord_cmpt_fr.sql 截为 eden_flat_ord_cmpt
>aaa='aa_bb_cc_dd_ee.sql'
>echo ${aaa%_*}
aa_bb_cc_dd
该shell位于~p482/script目录下,调用~p482/sql目录下的sql文件更新表中的数据
shell脚本:
#####################################################################
#
# Purpose: This script is to refresh tables, and log refresh
# information both in table and OS file
#
# Usage: refresh_table FILE_NAME
#
# Global varialbles
# FILE_NAME: the file needed by refresh
# USERNAME: database login ID
# PASSWORD: password for database user
# DATABASE: service name of database
# TABLE_NAME: the table to be refreshed
# START_TIME: when refresh begins
# END_TIME: when refresh ends
# BEFORE_ROWS: rows before refresh
# AFTER_ROWS: rows after refresh
# REFRESH_SEQ: the refresh sequence number for the table
#LOG_FILE: the file to store log information
# LOG_ROWS: a mid_stage parameter
#
# by ******
# 2004-12-03
#
#####################################################################
#!/usr/bin/bash
rfsh_env()
{
START_TIME=`date "+%Y-%m-%d %H:%M:%S"`
LOG_FILE=refreshlog.log
USERNAME=******
PASSWORD=******
DATABASE=dbname
}
rfsh_refresh()
{
TABLE_NAME=`echo ${1%_*}|cut -d"/" -f 3`
LOG_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<
SET FEED OFF
SET TERM OFF
SET HEAD OFF
SELECT count(*)
FROM refresh_log
WHERE user_name='$USERNAME'
AND obj_name='$TABLE_NAME';
EOF`
if [ $LOG_ROWS -eq 0 ]; then
REFRESH_SEQ=1
else
REFRESH_SEQ=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<
SET FEED OFF
SET TERM OFF
SET HEAD OFF
SELECT max(refresh_num+1)
FROM refresh_log
WHERE user_name='$USERNAME'
AND obj_name='$TABLE_NAME';
EOF`
fi
BEFORE_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<
SET FEED OFF
SET TERM OFF
SET HEAD OFF
select count(*) from $TABLE_NAME;
EOF`
START_TIME=`date "+%Y-%m-%d %H:%M:%S"`
sqlplus -s $USERNAME/$PASSWORD@$DATABASE>/dev/null<
INSERT INTO refresh_log(user_name,obj_name,obj_type,refresh_num,starttime,rows_b4_refresh)
VALUES('${USERNAME}','${TABLE_NAME}','T',${REFRESH_SEQ},sysdate,${BEFORE_ROWS} );
COMMIT;
spool /tmp/$TABLE_NAME.out
@$FILE_NAME
spool off
UPDATE refresh_log
SET endtime=sysdate
WHERE obj_name='$TABLE_NAME'
AND refresh_num=$REFRESH_SEQ;
COMMIT;
EXIT;
EOF
END_TIME=`date "+%Y-%m-%d %H:%M:%S"`
ERRORMSG=`cat /tmp/eden_flat_ord_cmpt.out|grep ORA-|grep -v ORA-00942|grep -v ORA-01418|head -n 1`
COUNT=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<
SET TERM OFF
SET HEAD OFF
SET FEED OFF
SELECT count(*) FROM user_tables WHERE table_name=upper('$TABLE_NAME');
EOF`
if [ $COUNT -eq 1 ]; then
REFRESH_STATUS='Y'
else
REFRESH_STATUS='N'
fi
AFTER_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<
SET FEED OFF
SET TERM OFF
SET HEAD OFF
select count(*) from $TABLE_NAME;
EOF`
sqlplus -s $USERNAME/$PASSWORD@$DATABASE>/dev/null<
UPDATE refresh_log
SET status='$REFRESH_STATUS',
total_time=(endtime-starttime)*3600*24,
rows_after_refresh=$AFTER_ROWS,
error='$ERRORMSG'
WHERE obj_name='$TABLE_NAME'
AND refresh_num=$REFRESH_SEQ;
COMMIT;
EXIT;
EOF
echo "owner: "$USERNAME>>$LOG_FILE
echo "table name: "$TABLE_NAME>>$LOG_FILE
echo "start time: "$START_TIME>>$LOG_FILE
echo "end time: "$END_TIME>>$LOG_FILE
echo "rows before refresh: "$BEFORE_ROWS>>$LOG_FILE
echo "rows after refresh: "$AFTER_ROWS>>$LOG_FILE
echo "refresh succeed(Y/N): "$REFRESH_STATUS>>$LOG_FILE
echo "error messages if any: "$ERRORMSG>>$LOG_FILE
echo " ">>$LOG_FILE
echo " ">>$LOG_FILE
}
#################################
## Main
#################################
if [ $# -lt 1 ]; then
echo " "
echo "Usage: refresh_table SQL_FILE_NAME"
echo " "
exit 1
fi
FILE_NAME=../sql/$1
if [ ! -e $FILE_NAME ]; then
echo " "
echo "File $FILE_NAME does NOT exist."
echo "Please specify an existed SQL file."
echo " "
exit 1
fi
rfsh_env
rfsh_refresh $FILE_NAME
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-783733/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/207/viewspace-783733/