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