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
# Check for comment lines, instances starting with '+ASM', and blank lines
if [[ $line = \#* ]] || [[ $line = +ASM* ]] || [[ -z $line ]]; then
continue
fi
# Extract SID and HOME from oratab line
DB_SID=$(echo $line | cut -d':' -f1)
DB_HOME=$(echo $line | cut -d':' -f2)
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