3

i often need to execute one and the same SQL script on multiple different Oracle DBs (different SIDs, but one and the same user and pass). So far my approach is to log on individually and execute the script individually, but this is quite time consuming. I checked out the option to use sqlplus cmd line script execution, but i see only the variant to log on to single DB. Could you recommenend an option , or possibly SQL developer plug in ?

Thank you.

Improve this question

5 Answers 5

Reset to default
3

One choice is to connect via a dblink from one database to all the others and execute the commands across that.

The feasibility depends on the type of commands, really.

Or course normally you'd use Enterprise Manager for this sort of stuff, but if you had that you'd probably have thought of it.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

The DB link option is also one which i have considered, but it is not a feasible solution as i am not allowed (not matter of priviledges ,but of operational procedures) to create DB links between the DBs.
That's a pretty sharp operational procedure for a company that doesn't mind the same user and password being used on the different databases.
I completely agree, yet, the fact is that i am not allowed to create DB links.
Install Oracle on your desktop. Then you can use database links from your PC to every computer. No one will ever know. And it's probably not violating the spirit of the restriction because the servers aren't talking directly to each other.
Technically feasible, but probably means having a file with all the relevant userid's and passwords on a desktop. There's ways to avoid that, but they're unlikely to get implemented, and the OP is setting himself up as the Big Security Problem in a company that already has dumb policies. There's no way I'd risk it, personally -- this is a company problem, not the OP's problem.
|
2

After some deliberation with my colleagues, i found a suitable solution that i would like to share with you : a .bat file with :

@echo off
FOR %%A IN (SID1 SID2 SID3) DO sqlplus -S user/pass@SID%%A @QFinfo.sql >> QFinfo.xls append
Improve this answer

1 Comment

I'd definitely look into using a wallet to store those passwords.
0

It somewhat depends on the output the script produces but I definitely would make some kind of batch file for the execution on all instances. Each execution would generate some log, they would be merged into one file and then you can check the results altogether. Especially if it is just a maintenance task.

Improve this answer

2 Comments

Yes , but connecting to the DBs in the batch is the part that eludes me.
Do you mean that you have to wait till the result of the first query before you can execute the second one?
0

Take a look at the open source program Method5. (Disclaimer: I am the author of that program.)

It lets you run a SQL or PL/SQL against any number of databases simultaneously. All the work is done within Oracle so it works with SQL Developer or any other IDE.

Install it on one central management database and configure it to connect to your databases. (This may be easy or painful, depending on how many databases you have and how standardized the environment is.) Then take any statement like this:

SQL> select * from dual;

D
-
X

And run it like this:

SQL> select * from table(m5('select * from dual'));

DATABASE_NAME  D
-------------- -
db1            X
db2            X
db3            X
db4            X
....

A few caveats:

  1. Currently it only supports SQL and PL/SQL, not SQL*Plus scripts. But if your SQL*Plus scripts are just a few simple statements you can easily wrap them in a PL/SQL block.
  2. This program was built for DBAs to rapidly run statements against hundreds of databases. If you only have a handful of databases it might not be worth the trouble.
  3. The system is built on database links and you mentioned that database links are not allowed because of operational procedures. There's nothing inherently unsafe about database links and I think the only reason organizations ban them is because people often misuse them. I went to great lengths to ensure that Method5 does not repeat any of those same mistakes. It doesn't use shared passwords, it removes DES password hashes, etc. See the file security.md on the project page for more details. That information, as well as the program being "commercial off-the-shelf software", may be enough to help you make a security exception.
Improve this answer

Comments

0

Try the below simple snippet

#!/bin/bash
cat service_names.txt | while read line
do
sqlplus -s username/password@$line @/u01/scripts/script.sql
done

## DB service names should be kept in different lines in "service_names.txt"
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.