Mailing lists

MuSQL - Runs Same SQL Script in Many Oracle Databases at Once

Release History

Download MuSQL 3.01

Ever wanted to quickly execute the same SQL query in several Oracle databases? Or to make identical changes in multiple databases, fast? With output stored in one document? And save those SQL statements (and reports) for later use? And be able to keep working on those queries and eventually turn them into advanced SQL*Plus scripts producing nice multi-database reports? As HTML? And to create more SQL scripts and reports like that? Or completely un-like that? And to select a previously created script from a list and run it simultaneously in all or some of your databases - as you wish? And compare results from different runs? And not to spend much time on anything else to support all this?

Well, you have come to the right place.

MuSQL is designed to simultaneously execute your SQL queries, DML statements, PL/SQL blocks, and entire SQL*Plus scripts in multiple Oracle databases quickly and effortlessly.

Why would you want to run the same SQL script in different databases?

Sometimes, a database user may have to deal with many databases that contain similar tables or entire schemas - in different environments, locations, versions, etc. That makes running identical queries or DML statements (updates) against these databases possible and useful - query results may be compared, or data may be quickly modified in a similar way.

However, it is database administrators who can benefit the most from MuSQL. The SQL queries and scripts they need to run in different databases are often identical. There is always a need to run one-off unscheduled queries or jobs. Say, you found that the data file for SYSTEM tablespace is not on Autoextend in one database, and you wonder, what is it in all other databases? Or you need to drop or lock a personal account after a user has left - everywhere. Or to verify if a schema for a DBA tool has been created - everywhere. Or to run a report for the Audit department - again, everywhere. This is exactly where MuSQL excels. Ability to simultaneously and quickly execute queries and scripts in multiple databases dramatically increases your efficiency and opens opportunities you never had before. (Oracle Grid allows users to execute SQL scripts, but it is costly and has to be installed and configured on all database servers.)

All you have to do with MuSQL is to come up with a query or SQL script, specify the databases in which you want to run it, a user ID and password, and MuSQL will log on to each database and run your script. Want to save the output from all databases to one file? You got it. Want to run it again and save to another file? No problem. Send output from each database to a separate file? Save the script, database selection, and all other options for a later use? Save a default database selection and user ID so that you do not have to specify them for new jobs? All these and other features are available to help you be most efficient with MuSQL and your databases.

Here is what is required for installing and using MuSQL:

  • MuSQL is installed on and connects to Oracle databases from a Windows machine. It connects to databases remotely using available Oracle Database client software and Oracle Net service names. The target databases can run on any platform. MuSQL requires no special installation in target databases or on their host servers.

  • The queries and scripts you want to execute must come from you. MuSQL does not provide them. The accounts with which you connect to databases must have sufficient database privileges to connect to databases and execute all statements in your script.

  • You have to provide authentication (a user ID and password) for every database. MuSQL will be most efficient if user IDs and passwords are the same ("synchronized") in all databases selected for a job - you will have to enter them only once. To maintain the password synchronized, use another BS Utilities tool, PassAid. In some cases, MuSQL can even handle situations where user ID and/or password in all selected databases are different but follow a certain pattern. MuSQL may still be able to log in and run the script.

After MuSQL is started, you open a previously saved job or create a new job - provide your SQL statement or script (write it, or copy from somewhere else and and paste it, or use a SQL*Plus @ command to call a SQL script located elsewhere), and specify a list of databases and a user ID to be used for connections to those databases. You can save a database selection and user ID to be used as the default by new jobs. You can also specify report (spooling) and other options.

Once a job is open, you can execute it. At this moment, you enter a password (if it is common for all databases - otherwise, you have to enter a password for each database separately). Then, MuSQL loops through all selected databases, consecutively connecting to each of them and executing your script using Oracle Database client's sqlplus utility or SQL Developer Comand Line tool SQLcl. If MuSQL encounters a problem with connection or authentication, it will stop and ask you whether you want to continue with the remaining databases or cancel the job execution.

You can create one or more persistent database groups that you will then be able to select for script execution. The same database groups can be used by other BS Utilities tools, like PassAid.

If you really need to run the same SQL scripts in multiple databases, you will certainly benefit from using MuSQL. You can even get addicted to it. It is fun!

Show your databases who's the boss. Use your MuSQL !

MuSQL is currently FREE !

Download MuSQL 3.01 - the latest release