shell refresh_table

用于调用一个sql文件刷新一张表，并纪录刷新信息

[@more@]

用法：refresh_table SQL_FILE_NAME SCHEMA PASSWORD

$refresh_table eden_flat_ord_cmpt_fr.sql edenfr ******

该脚本中注意以下语句：

TABLE_NAME=`echo ${1%_*}|cut -d"/" -f 4`

其中${1%_*}是bash里面的模式匹配。shell refresh_table位于目录/erdb/bin下，而sql文件位于/erdb/sql目录下，sql文件名的命名规则为TABLENAME_COUNTRY.sql，其中COUNTRY为缩写，FR代表法国，UK代表英国等。

通过修改函数rfsh_env()中的参数，可以方便连到不同的数据库（比如开发库和生产库），也可以修改日志文件目录等。

shell refresh_table的脚本如下：

#####################################################################
#
# Purpose: This script is to refresh tables, and log refresh
#     information both in table and OS file
#
# Usage: refresh_table SQL_FILE_NAME SCHEMA SCHEMA_PASS
#
# Global varialbles
# FILE_NAME: the file needed by refresh
# USERNAME: database login ID
# PASSWORD: password for database user
# DATABASE: service name of database
# SCHEMA: the owner of objects to be refreshed
# SCHEMA_PASS: the password for the user SCHEMA
# 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_PATH: where the log file locates
# LOG_FILE: the file to store log information
#     file name in format refresh_log.yyyy-mm-dd
# LOG_DETAIL: the file to log detail information
#     file name in format log_detail.SCHEMA.yyyy-mm-dd
# LOG_ROWS: a mid_stage parameter
#
#
#####################################################################

#!/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=******
DATABASE=service_name
}

rfsh_refresh()
{
TABLE_NAME=`echo ${1%_*}|cut -d"/" -f 4`

LOG_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<
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<
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<
        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 >$LOG_PATH/$LOG_DETAIL$SCHEMA/$SCHEMA_PASS@$DATABASE>>$LOG_PATH/$LOG_DETAIL<

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
set head off
@$FILE_NAME
select 'updating table eden_refr_log...' from dual;
set head on
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;