Oracle数据库循环重建多个物化视图shell脚本

#!/bin/bash
LOG_FILE="script_output_$(date +'%Y%m%d_%H%M').log"

exec >"$LOG_FILE" 2>&1
# Set database connection information
DB_HOST="LOCALHOST"
DB_PORT="1521"
DB_SID="pdb"
DB_USER="mics"
DB_PASS="123456"
TNS_SERVICE="${DB_SID}"

START_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo "Script start time: $START_TIME"

# Define the list of materialized view names
MV_NAMES=("MV_CDE_STATION" "MV_CDE_DEVICE") # Modify to your materialized view names

# Loop through each materialized view
for MV_NAME in "${MV_NAMES[@]}"; do

LOOP_START_TIME=$(date +%s)
echo "Checking materialized view: $MV_NAME"

# Use here document to execute SQL query, ensure correct format
RESULT=$(sqlplus -s "$DB_USER/$DB_PASS@//${DB_HOST}:${DB_PORT}/${TNS_SERVICE}" <<EOF
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
SELECT COUNT(*) FROM all_mviews WHERE mview_name = UPPER('$MV_NAME');
EXIT;
EOF
)

# Remove unnecessary characters such as spaces and newlines
RESULT=$(echo "$RESULT" | tr -d '[:space:]')

# Check if RESULT is a valid number
if [[ "$RESULT" =~ ^[0-9]+$ ]]; then
if [ "$RESULT" -gt 0 ]; then

# Get the SQL to create the materialized view
CREATE_MV_SQL=$(sqlplus -s "$DB_USER/$DB_PASS@//${DB_HOST}:${DB_PORT}/${TNS_SERVICE}" <<EOF
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
SET LONG 10000
SET LONGCHUNKSIZE 10000
SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW',UPPER('$MV_NAME'),UPPER('$DB_USER')) FROM dual;
EXIT;
EOF
)
# Remove extra newlines and spaces
CREATE_MV_SQL=$(echo "$CREATE_MV_SQL" | tr -d '\n' | tr -s ' ')
CREATE_MV_SQL="${CREATE_MV_SQL};"
echo $CREATE_MV_SQL > create_mv_view.sql
echo "commit;" >> create_mv_view.sql
echo "exit;" >> create_mv_view.sql
echo "Materialized view $MV_NAME exists, deleting the old materialized view..."

# Delete the existing materialized view
DELETE_MV_SQL="DROP MATERIALIZED VIEW $MV_NAME;"
# Execute the SQL to delete the materialized view
echo "$DELETE_MV_SQL" | sqlplus -s "$DB_USER/$DB_PASS@//${DB_HOST}:${DB_PORT}/${TNS_SERVICE}"

echo "Materialized view $MV_NAME has been deleted, recreating..."

# Execute the SQL to create the materialized view
sqlplus -s "$DB_USER/$DB_PASS@//${DB_HOST}:${DB_PORT}/${TNS_SERVICE}" @create_mv_view.sql > create_mview.log 2>&1

echo "Materialized view $MV_NAME has been recreated."

else
echo "Materialized view $MV_NAME does not exist, please create the view manually first"
fi

else
# If RESULT is not a valid number
echo "Query result is invalid or empty, skipping materialized view $MV_NAME"
fi

# Record the end time and calculate the elapsed time
LOOP_END_TIME=$(date +%s)
DURATION=$((LOOP_END_TIME - LOOP_START_TIME))

if [ "$DURATION" -ge 60 ]; then
echo "Materialized view $MV_NAME processing completed, elapsed time: $((DURATION / 60)) minutes"
else
echo "Materialized view $MV_NAME processing completed, elapsed time: $DURATION seconds"
fi

echo "------"
done

END_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo "Script end time: $END_TIME"