Collecting Oracle SQL output from a script

I recently had to pick up some data for documenting what connects to what, and the easiest way appeared to be to start at the databases and work back through the 3-tier setup.

Heres a rough and ready way to get the info, we'll talk about improving the output later.

for each_SID in $(grep -v ^# /etc/oratab | egrep -v "^\+ASM|^agent" | cut -d: -f1)
do
 echo "DATABASE: ${each_SID}"
 ORAENV_ASK=NO
 ORACLE_SID=${each_SID}
 ORACLE_HOME=$(grep ${ORACLE_SID} /etc/oratab | cut -d: -f2)
 export ORAENV_ASK ORACLE_SID ORACLE_HOME
 .  ${ORACLE_HOME}/bin/oraenv
 ${ORACLE_HOME}/bin/sqlplus / as SYSDBA << EOSQL
  select distinct username,machine from v\$session;
  quit
EOSQL
done > /tmp/my_file_of_guff


REMEMBER: heredoc syntax is a PITA. The closing EOSQL (or whatever you use has to be the first thing on the line, no whitespace. You can use <<-EOSQL and close with at tab and your string, e.g. <TAB>EOSQL. Thanks to here for reminding me about this. I've got some notes about prefixing your string with ! but I can't remember for the life of me what it does.

The first thing you'll notice about your output is that its horrible. If you've used SQLPlus before you know its a turd that really isn't acceptable for the 21st century and won't have been surprised. We can however make things a little easier on ourselves.

These should help with the output getting wrapped...

SET WRAP OFF
SET PAGESIZE 0

And should you need to output to different files per database, using our example above, we can use the spool command to send the output to a file. It's still not great, but its better.

for each_SID in $(grep -v ^# /etc/oratab | egrep -v "^\+ASM|^agent" | cut -d: -f1)
do
 echo "DATABASE: ${each_SID}"
 ORAENV_ASK=NO
 ORACLE_SID=${each_SID}
 ORACLE_HOME=$(grep ${ORACLE_SID} /etc/oratab | cut -d: -f2)
 export ORAENV_ASK ORACLE_SID ORACLE_HOME
 .  ${ORACLE_HOME}/bin/oraenv
 ${ORACLE_HOME}/bin/sqlplus / as SYSDBA << EOSQL
  SET WRAP OFF
  SET PAGESIZE 0
  SPOOL /tmp/${ORACLE_SID}.extract
  select distinct username,machine from v\$session;
  SPOOL OFF
  quit
EOSQL
done