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