[Oracle] Execute SQL queries in multiple DB instances

Hi everybody,

If you are an Oracle DBA in a company, you have surelly get asked from someone to write a script in order to retrieve some information from multiple DB. In this article I’ll give you a simple bash script that you can re-use to execute a block of SQL statements in multiple instances located in the same machine.

1 – Getting Oracle SID

The first function, query all the PMON processes in your machine, clean up the strings and write the results in a file called oracle_sid.txt

get_oracle_sid() {
ps -ef | grep pmon|awk '{print $NF}'|sed 's/_/ /g'|awk '{print $NF}'| sort -u| grep -v pmo| grep -v MGM > oracle_sid.txt 
}

2 – Execute SQL statement

The second function reads the SID (written before in the file oracle_sid.txt) in a for loop, sets the multiple HOME environment using official Oracle oraenv script and executes the SQL statements you need. If you want to write the results in a file, you can simply add the clausole spool filename.txt append.

execute_sql_statement() {
for a in $(cat oracle_sid.txt); do
. oraenv <<< $a
sqlplus / as sysdba << EOF
spool result.txt append
          select username from dba_users;
EOF
done

3 – Main

In the end, you simply launch the two functions calling them:

# MAIN:
get_oracle_sid
execute_sql_statement