Pages

Thursday, November 15, 2012

how to execute same SQL script in all Oracle databases in Linux server [single shot]


Linux shell script: sample


[orcl@qact /home/oracle/adt]$ more all_db.sh 


#!/bin/bash

for I in GTD1 PTD1 FTD1 CTD1 MTD1 MSD1 # List of DB names
do
  echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
  echo "executing scripts...."
  export ORACLE_SID=$I
  echo "DB Name: "$ORACLE_SID
  sqlplus -S -L  / as sysdba < /home/oracle/adt/adt.sql
  echo "Done."
done

[orcl@qact /home/oracle/adt]$ more adt.sql 

spool /home/oracle/adt/DB_result_$ORACLE_SID.log
set line 280
select name, open_mode, log_mode from v$database;
.
.
.
spool off;
exit;



[orcl@qact /home/oracle/adt]$ ./all_db.sh

[orcl@qact /home/oracle/adt]$ ll

total 8308
-rwxr--r-- 1 oracle dba    2567 Nov 15 12:38 adt.sql
-rwxr--r-- 1 oracle dba     314 Nov 15 12:02 all_db.sh
-rw-r--r-- 1 oracle dba   96709 Nov 15 12:39 DB_result_GTD1.log
-rw-r--r-- 1 oracle dba   90588 Nov 15 12:39 DB_result_PTD1.log
-rw-r--r-- 1 oracle dba   64756 Nov 15 12:39 DB_result_FTD1.log
-rw-r--r-- 1 oracle dba   92605 Nov 15 12:39 DB_result_CTD1.log
-rw-r--r-- 1 oracle dba   92408 Nov 15 12:39 DB_result_MTD1.log
-rw-r--r-- 1 oracle dba   92408 Nov 15 12:39 DB_result_MSD1.log



Note: " sqlplus -S -L " 

-S silent.
-L try login only once. (else 3 times)


No comments:

Post a Comment