Bash Script to Connect to Multiple Oracle Databases and Run Query

If you need to run the same query on all the databases hosted on your server, you could use this bash script.

The script checks all Oracle instances in /etc/oratab, but it skips any lines commented.

It also skips all ASM instances.

You should create a script with the query you want to run and pass it as an argument to the bash script.

Create a new file named run_on_all.sh and put this content inside:

#!/bin/bash

# Oratab location
ORATAB=/etc/oratab

# SQL script file passed as an argument
SQL_FILE=$1

# Log file with timestamp in filename
LOG_FILE="run_on_all_dbs_$(date +%Y%m%d_%H%M%S).log"

# Check if the oratab file exists
if [ ! -f "$ORATAB" ]; then
    echo "Oratab file not found at $ORATAB" | tee -a $LOG_FILE
    exit 1
fi

# Check if the SQL file exists
if [ ! -f "$SQL_FILE" ]; then
    echo "SQL file not found at $SQL_FILE" | tee -a $LOG_FILE
    exit 1
fi

# Iterate over databases in oratab
while read -r line
do
    if [[ $line = \#* ]]; then
        # Ignore comment lines
        continue
    fi

    # Extract SID and HOME from oratab line
    DB_SID=`echo $line | cut -d':' -f1`
    DB_HOME=`echo $line | cut -d':' -f2`

    # Exclude instances starting with '+ASM'
    if [[ $DB_SID = +ASM* ]]; then
        continue
    fi

    echo "Running SQL script on database: $DB_SID" | tee -a $LOG_FILE

    # Export necessary environment variables
    export ORACLE_SID=$DB_SID
    export ORACLE_HOME=$DB_HOME
    export PATH=$ORACLE_HOME/bin:$PATH

    # Run the SQL script and log the output
    sqlplus -s / as sysdba @$SQL_FILE | tee -a $LOG_FILE

done < "$ORATAB"

Then you give it execution rights.

chmod +x run_on_all.sh

And you run it like that, supposing you have an Oracle script called script.sql

./run_on_all.sh script.sql

Leave a Reply Cancel reply