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

Script to execute the SQL Query on all Oracle databases present on 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.

Advertisements

One thought on “Script to execute the SQL Query on all Oracle databases present on Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.