Script to execute the SQL Query on all Oracle databases present on Single Server

Following Shell script help to run one query in all the databases of one Server:

Note: You can change the script as you wish. i am using tablespace space checkup script.

Step 1: Save the following file as check.sh at oratab file location:

ORATAB=/etc/oratab

if [ ! -f $ORATAB ]
then
exit 1
fi

# Loop for every entry in ORATAB file
cat $ORATAB|grep -v '^$' | while read LINE
do        case $LINE in
\#*)    # Comment-Line in ORATAB
;;        *)
# Setup ORACLE_SID and ORACLE_HOME

# if third field in ORATAB is 'Y'
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`

if [ "$ORACLE_SID" = '*' ]
then
ORACLE_SID=""
fi

ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
echo ORACLE_SID=$ORACLE_SID >> $Check.log
echo ORACLE_HOME=$ORACLE_HOME
export ORACLE_SID=$ORACLE_SID
export ORACLE_HOME=$ORACLE_HOME
exit| $ORACLE_HOME/bin/sqlplus / as sysdba @query.sql >> Check.log
esac # End case $LINE
done
# End Loop read ORATAB

Step 2: Save one more file as query.sql

set linesize 400
col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999
SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

Note: Specify the command to run in query.sql

Step 3: Run the script:

sh check.sh

It will generate check.out file having output of all databases running on server.