Monday, February 9, 2009

Script to connect all the database on a server and run a SQL Script

Script to connect all the database on a server and run a SQL Script
!#!/bin/sh
for SID in `ps -ef grep pmon grep -v grep awk -F_ '{print $3}' sort `
do
ORACLE_SID=$SID
export ORACLE_SID
ORACLE_HOME=`grep $SID /var/opt/oracle/oratab awk -F: '{print $2}'`
export ORACLE_HOME
sqlplus "/as sysdba" << EOF
select name from v\$database;
EOF
Done
Please run this script only for any select operations, any updates or modifications in the SQL script It is suggested do them manually.
Note :- The above script check for oratab file in /var/opt/oracle/ path, if you want to run the script in LINUX or any other UNIX OS please change the oratab location to /etc/oratab
Include your script in place of “select name from v$databae” ( between the two EOFs)

No comments: