- 论坛徽章:
- 0
|
运行脚本时，前面加不加sh有什么区别？
该脚本主要是根据传入的参数去刷新一张表，并纪录相应的日志信息。包含两个函数，rfsh_env和rfsh_refresh
opserdb>cat refresh_table
#!/usr/bin/bash
rfsh_env()
{
LOG_PATH=/erdb/log
LOG_FILE=refresh_log.`date "+%Y-%m-%d"`
LOG_DETAIL=log_detail.$SCHEMA.`date "+%Y-%m-%d"`
USERNAME=eden
PASSWORD=eden
DATABASE=perdb
}
rfsh_refresh()
{
TABLE_NAME=`echo ${1%_*}|cut -d"/" -f 3`
LOG_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<<EOF
SET FEED OFF
SET TERM OFF
SET HEAD OFF
SELECT count(*)
FROM eden_refr_log
WHERE user_nam='$SCHEMA'
AND obj_nam='$TABLE_NAME';
EOF`
if [ $LOG_ROWS -eq 0 ]; then
REFRESH_SEQ=1
else
REFRESH_SEQ=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<<EOF
SET FEED OFF
SET TERM OFF
SET HEAD OFF
SELECT max(refr_seq_nb+1)
FROM eden_refr_log
WHERE user_nam='$SCHEMA'
AND obj_nam='$TABLE_NAME';
EOF`
fi
BEFORE_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<<EOF
SET FEED OFF
SET TERM OFF
SET HEAD OFF
select count(*) from $SCHEMA.$TABLE_NAME;
EOF`
START_TIME=`date "+%Y-%m-%d %H:%M:%S"`
echo "-----------------------------------------">>$LOG_PATH/$LOG_DETAIL
echo "start refreshing $TABLE_NAME ..." >>$LOG_PATH/$LOG_DETAIL
echo "-----------------------------------------">>$LOG_PATH/$LOG_DETAIL
sqlplus -s $SCHEMA/$SCHEMA_PASS@$DATABASE>>$LOG_PATH/$LOG_DETAIL<<EOF
INSERT INTO $USERNAME.eden_refr_log(user_nam,obj_nam,obj_type,refr_seq_nb,strt_dat_tim,rows_bef_refr)
VALUES('${SCHEMA}','${TABLE_NAME}','T',${REFRESH_SEQ},sysdate,$BEFORE_ROWS);
COMMIT;
spool /tmp/$SCHEMA_$TABLE_NAME.out
@$FILE_NAME
spool off
UPDATE $USERNAME.eden_refr_log
SET end_dat_tim=sysdate
WHERE user_nam='$SCHEMA'
AND obj_nam='$TABLE_NAME'
AND refr_seq_nb=$REFRESH_SEQ;
COMMIT;
EXIT;
EOF
END_TIME=`date "+%Y-%m-%d %H:%M:%S"`
ERRORMSG=`cat /tmp/$SCHEMA_$TABLE_NAME.out|grep ORA-|head -n 1`
ERR_COUNT=`cat /tmp/$SCHEMA_$TABLE_NAME.out|grep ORA-|wc -l`
if [ $ERR_COUNT -eq 0 ]; then
REFRESH_STATUS='Y'
RETURN_VAL=0
else
REFRESH_STATUS='N'
RETURN_VAL=1
fi
sqlplus -s $USERNAME/$PASSWORD@$DATABASE>>$LOG_PATH/$LOG_DETAIL<<EOF
UPDATE eden_refr_log
SET sta_cod='$REFRESH_STATUS',
tot_drtn_tim=( end_dat_tim - strt_dat_tim )*3600*24,
err_txt='$ERRORMSG'
WHERE user_nam='$SCHEMA'
AND obj_nam='$TABLE_NAME'
AND refr_seq_nb=$REFRESH_SEQ;
COMMIT;
EXIT;
EOF
AFTER_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<<EOF
SET FEED OFF
SET TERM OFF
SET HEAD OFF
select count(*) from $SCHEMA.$TABLE_NAME;
EOF`
sqlplus -s $USERNAME/$PASSWORD@$DATABASE>>$LOG_PATH/$LOG_DETAIL<<EOF
UPDATE eden_refr_log
SET rows_aft_refr=$AFTER_ROWS
WHERE user_nam='$SCHEMA'
AND obj_nam='$TABLE_NAME'
AND refr_seq_nb=$REFRESH_SEQ;
COMMIT;
EXIT;
EOF
echo "owner: "$SCHEMA>>$LOG_PATH/$LOG_FILE
echo "table name: "$TABLE_NAME>>$LOG_PATH/$LOG_FILE
echo "start time: "$START_TIME>>$LOG_PATH/$LOG_FILE
echo "end time: "$END_TIME>>$LOG_PATH/$LOG_FILE
echo "rows before refresh: "$BEFORE_ROWS>>$LOG_PATH/$LOG_FILE
echo "rows after refresh: "$AFTER_ROWS>>$LOG_PATH/$LOG_FILE
echo "refresh succeed(Y/N): "$REFRESH_STATUS>>$LOG_PATH/$LOG_FILE
echo "error messages if any: "$ERRORMSG>>$LOG_PATH/$LOG_FILE
echo " ">>$LOG_PATH/$LOG_FILE
echo " ">>$LOG_PATH/$LOG_FILE
}
#################################
## Main
#################################
if [ $# -lt 3 ]; then
echo " "
echo "Usage: refresh_table SQL_FILE_NAME SCHEMA SCHEMA_PASS"
echo " "
exit 1
fi
FILE_NAME=`echo $1|tr "[A-Z]" "[a-z]"`
FILE_NAME=../sql/$FILE_NAME
SCHEMA=`echo $2|tr "[A-Z]" "[a-z]"`
SCHEMA_PASS=`echo $3|tr "[A-Z]" "[a-z]"`
if [ ! -e $FILE_NAME ]; then
echo " "
echo "File $FILE_NAME does NOT exist."
echo " lease specify an existed SQL file."
echo " "
echo " ">>$LOG_PATH/$LOG_FILE
echo "----------------------------------">>$LOG_PATH/$LOG_FILE
echo " REFRESHING `echo ${1%_*}|cut -d"/" -f 3` ...">>$LOG_PATH/$LOG_FILE
echo "----------------------------------">>$LOG_PATH/$LOG_FILE
echo "File $FILE_NAME does NOT exist! ">>$LOG_PATH/$LOG_FILE
exit 1
fi
rfsh_env
rfsh_refresh $FILE_NAME $SCHEMA $SCHEMA_PASS
if [ $RETURN_VAL -eq 1 ]; then
echo ""
echo "Errors during refresh table $TABLE_NAME."
echo ""
fi
exit $RETURN_VAL |
|
免费注册
发表于 2005-01-17 19:39
