I have some standard SQL that I want to run against multiple databases on a single server to help me report informations or diagnose problems.

This script intended for Oracle DBA, executes an sql statement on all Oracle 18c databases running on a Unix server.

First example:       On all databases.
Second example: On a list of ORACLE_SID

Let’s do that!

-- ################################################
-- # Creator: Cyrille Modiano (DBA Oracle
-- # Created: 2011/09/08 Last Validation: sept. 2018
-- # Name: MassExecuteSQL
-- ################################################
-- #
-- # Compatible: Oracle 18c, 12c, 11g, 10g, 8i, 9i
-- #
-- ################################################

for base in `ps -ef | grep pmon | grep -v grep | awk -F_ '{print $3}'`
do
export ORACLE_SID=$base
export ORAENV_ASK=NO
. oraenv
echo Database $base : >> resultat.log
echo "          " >> resultat.log
echo "select status from v\$instance;" | sqlplus -s "/ as sysdba" >> resultat.log
done

# With a list of databases

for base in BASE1 BASE2 BASE3 BASE4 BASE5
do
export ORACLE_SID=$base
export ORAENV_ASK=NO
. oraenv
echo Database $base : >> resultat.log
echo "          " >> resultat.log
echo "select status from v\$instance;" | sqlplus -s "/ as sysdba" >> resultat.log
done