一個資料倉儲資料重新整理的實現機制（七）

shell refresh_schema

該shell透過呼叫上面的兩個procedures和shell refresh_table，重新整理某個國家的所有要重新整理的資料庫物件

[@more@]

該指令碼自動讀取表eden_refr_objt中的內容，重新整理某個國家的資料庫物件

用法：Usage: refresh_schema SCHEMA SCHEMA_PASSWORD SCHEMA2 SCHEMA_PASSWORD2

$refresh_schema edenfr password1 ewrfrmaster password2

其中灰掉部分程式碼基本上重複上面的程式碼，只是處理schema2所屬的資料庫物件。這樣的寫法好像不是很好，也沒想到更合理的方法，先這樣吧。

該shell指令碼如下：

###################################################################
#
# Fiel Name: refresh_schema
#
# Purpose:  To refresh all the necessary Matarialized Views and
#     Tables for a specified schema, log refresh information also.
#     If there are more MVs or Tables to be refreshed, please
#     change this script and add commands here
#
# Usage: refresh_schema SCHEMA SCHEMA_PASSWORD SCHEMA2 SCHEMA_PASSWORD2
#
# Global varialbles
# LOG_PATH: where does the log file locate
# LOG_FILE: the file to log information,
#     file name in format refresh_log.yyyy-mm-dd
# LOG_DETAIL: the file to log detail inforamtion
#     file name in format refresh_log.SCHEMA.yyyy-mm-dd
# USERNAME: the database user name
# PASSWORD: the password of the user
# DATABASE: the service name of database
# WEEK_DAY: the day of current day
# OBJECT_TYPE: object type.
#              V: materialized views
#        T: table,refreshed by SQL file
#              P: table, refreshed by stored procedure 
# REFRESH_TYPE: refresh type
#         W: weekly
#         D: daily
#         N: none or never
# PROC_NAME: the procedure to refresh a specified 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=****
DATABASE=service_name
}

rfsh_schema(){
echo "START REFRESHING $SCHEMA......">$LOG_PATH/$LOG_DETAIL
echo "`date`">>$LOG_PATH/$LOG_DETAIL
echo " ">>$LOG_PATH/$LOG_DETAIL
echo " ">>$LOG_PATH/$LOG_DETAIL

for OBJECT_NAME in `sqlplus -s
 SET FEED OFF
 SET HEAD OFF
 SET TERM OFF
 SELECT lower(obj_nam)
   FROM eden_refr_objt
   WHERE lower(user_nam)='$SCHEMA'
   ORDER BY obj_seq ASC;
EOF`
do
  REFRESH_TYPE=`sqlplus -s
 SET FEED OFF
 SET HEAD OFF
 SET TERM OFF
 SELECT upper(refr_type)
   FROM eden_refr_objt
   WHERE lower(user_nam)='$SCHEMA'
   AND lower(obj_nam)='$OBJECT_NAME';
EOF`

WEEK_DAY=`date "+%w"`

if [ $WEEK_DAY = "6" ] && [ $REFRESH_TYPE = "W" ] || [ $REFRESH_TYPE = "D" ]; then
  OBJECT_TYPE=`sqlplus -s
  SET TERM OFF
  SET HEAD OFF
  SET FEED OFF
  SELECT upper(trim(obj_type))
    FROM eden_refr_objt
    WHERE lower(user_nam)='$SCHEMA'
    AND lower(obj_nam)='$OBJECT_NAME';
EOF`

  if [ $OBJECT_TYPE = "V" ]; then
    echo "----------------------------------------------------">>$LOG_PATH/$LOG_DETAIL
    echo "start refreshing $SCHEMA.$OBJECT_NAME ...">>$LOG_PATH/$LOG_DETAIL
    echo "----------------------------------------------------">>$LOG_PATH/$LOG_DETAIL
    sqlplus -s
      set serveroutput on
      execute EDEN_REFRESH.REFRESH_MV('$SCHEMA','$OBJECT_NAME','$LOG_PATH');
      exit;
EOF

  elif [ $OBJECT_TYPE = "T" ]; then
    SQL_FILE=${OBJECT_NAME}_${COUNTRY}.sql
    /usr/bin/bash /erdb/bin/refresh_table $SQL_FILE $SCHEMA $SCHEMA_PASS

  elif [ $OBJECT_TYPE = "P" ]; then
    echo "----------------------------------------------------">>$LOG_PATH/$LOG_DETAIL
    echo "start refreshing $SCHEMA.$OBJECT_NAME ...">>$LOG_PATH/$LOG_DETAIL
    echo "----------------------------------------------------">>$LOG_PATH/$LOG_DETAIL
    PROC_NAME=`sqlplus -s
  SET FEED OFF
  SET HEAD OFF
  SET TERM OFF
  SELECT proc_nam
    FROM eden_refr_objt
    WHERE lower(user_nam)='$SCHEMA'
    AND lower(obj_nam)='$OBJECT_NAME';
EOF`
    PROC_NAME=`echo $PROC_NAME|sed -e "s/^n//g"`
    sqlplus -s
      set serveroutput on
      execute EDEN_REFRESH.REFRESH_TAB('$SCHEMA','$OBJECT_NAME','$PROC_NAME','$LOG_PATH');
      exit;
EOF

  else
    echo "Error: type should be in V / T / P">>$LOG_PATH/$LOG_DETAIL
  fi
fi

done

for OBJECT_NAME in `sqlplus -s
 SET FEED OFF
 SET HEAD OFF
 SET TERM OFF
 SELECT lower(obj_nam)
   FROM eden_refr_objt
   WHERE lower(user_nam)='$SCHEMA2'
   ORDER BY obj_seq ASC;
EOF`
do
  REFRESH_TYPE=`sqlplus -s
 SET FEED OFF
 SET HEAD OFF
 SET TERM OFF
 SELECT upper(refr_type)
   FROM eden_refr_objt
   WHERE lower(user_nam)='$SCHEMA2'
   AND lower(obj_nam)='$OBJECT_NAME';
EOF`

WEEK_DAY=`date "+%w"`

if [ $WEEK_DAY = "6" ] && [ $REFRESH_TYPE = "W" ] || [ $REFRESH_TYPE = "D" ]; then
  OBJECT_TYPE=`sqlplus -s
  SET TERM OFF
  SET HEAD OFF
  SET FEED OFF
  SELECT upper(trim(obj_type))
    FROM eden_refr_objt
    WHERE lower(user_nam)='$SCHEMA2'
    AND lower(obj_nam)='$OBJECT_NAME';
EOF`

  if [ $OBJECT_TYPE = "V" ]; then
    echo "----------------------------------------------------">>$LOG_PATH/$LOG_DETAIL
    echo "start refreshing $SCHEMA2.$OBJECT_NAME ...">>$LOG_PATH/$LOG_DETAIL
    echo "----------------------------------------------------">>$LOG_PATH/$LOG_DETAIL
    sqlplus -s
      execute EDEN_REFRESH.REFRESH_MV('$SCHEMA2','$OBJECT_NAME','$LOG_PATH');
      exit;
EOF

  elif [ $OBJECT_TYPE = "T" ]; then
    SQL_FILE=${OBJECT_NAME}_${COUNTRY}.sql
    /usr/bin/bash /erdb/bin/refresh_table $SQL_FILE $SCHEMA2 $SCHEMA_PASS2

  elif [ $OBJECT_TYPE = "P" ]; then
    echo "----------------------------------------------------">>$LOG_PATH/$LOG_DETAIL
    echo "start refreshing $SCHEMA2.$OBJECT_NAME ...">>$LOG_PATH/$LOG_DETAIL
    echo "----------------------------------------------------">>$LOG_PATH/$LOG_DETAIL
    PROC_NAME=`sqlplus -s
  SET FEED OFF
  SET HEAD OFF
  SET TERM OFF
  SELECT proc_nam
    FROM eden_refr_objt
    WHERE lower(user_nam)='$SCHEMA2'
    AND lower(obj_nam)='$OBJECT_NAME';
EOF`
    PROC_NAME=`echo $PROC_NAME|sed -e "s/^n//g"`
    sqlplus -s
      execute EDEN_REFRESH.REFRESH_TAB('$SCHEMA2','$OBJECT_NAME','$PROC_NAME','$LOG_PATH');
      exit;
EOF

  else
    echo "Error: type should be in V / T / P">>$LOG_PATH/$LOG_DETAIL
  fi
fi

done

echo "     ">>$LOG_PATH/$LOG_DETAIL
echo "`date`">>$LOG_PATH/$LOG_DETAIL
echo "  REFRESHING $SCHEMA FINISHED!">>$LOG_PATH/$LOG_DETAIL
echo "----------------------------------------------------">>$LOG_PATH/$LOG_DETAIL

ERR_COUNT=`cat $LOG_PATH/$LOG_DETAIL|grep ORA-|wc -l`
if [ $ERR_COUNT -gt 0 ]; then
  echo " "
  echo "ERRORS during refresh!!!"
  echo "please check file $LOG_PATH/$LOG_DETAIL for detail information."
  echo " "
  exit 1
else
  echo " "
  echo "Refresh succeed"
  exit 0
fi
}

#########################################
#
# Main
#
#########################################

if [ $# -lt 4 ]; then
  echo " "
  echo "Usage: refresh_schema SCHEMA SCHEMA_PASSWORD SCHEMA2 SCHEMA_PASSWORD2"
  echo " "
  exit
fi

SCHEMA=`echo $1|tr "[A-Z]" "[a-z]"`
SCHEMA_PASS=`echo $2|tr "[A-Z]" "[a-z]"`

SCHEMA2=`echo $3|tr "[A-Z]" "[a-z]"`
SCHEMA_PASS2=`echo $4|tr "[A-Z]" "[a-z]"`

COUNTRY=`echo $1|awk '{print substr($1,length($1)-1,length($1))}'`

rfsh_env

rfsh_schema