Introduction

Often, Oracle database administrators need to run commands on multiple databases at once. With csqlplus, a wrapper for the sqlplus command, a DBA can quickly query multiple databases simultaneously.

Basically, csqlplus is a simple wrapper script to call sqlplus for all databases specified in a file.

Usage

To use csqlplus, you will need to create an inventory file of your databases to query and make sure your system is configured correctly. Before executing csqlplus, make sure your system works as expected:

• Your TNS names resolution works (can be tested with tnsping)
• sqlplus and tnsping are in your current PATH

After downlading the latest version, you can launch csqlplus with the -h argument to get a quick overview:

$ ./csqlplus.sh -h
usage: ./csqlplus.sh <-q "query"|-f "filename"> <-i "inventory_file">
	  <-p "password"> [-u "username"] [-c] [-v] [-h] [-?]

Tool to query multiple databases in one command, useful in large environments

OPTIONS:
   -h      Show this message
   -q	   Query to be executed. Provide this in quotes (""), don't forget ";"
   -f	   File that contains the query
   -i	   Inventory file containing all databases to be queried
   -u	   Username to be used for queries (Defaults to 'simon')
   -p	   Password to be used for queries
   -c	   Enable concurrency
   -v      Verbose

Examples

Execute the query SELECT * FROM dual; on all databases in the inventory file inventory.txt as user "simon" and the password "tiger":

./csqlplus.sh  -q "SELECT * FROM dual;" -i inventory.txt -u simon -p "tiger" -v

Alternatively, you may specify the query in a query file instead on the command line:

./csqlplus.sh  -f query.sql -i inventory.txt -u simon -p "tiger" -v

Inventory file

In an inventory file, you specify which databases will be queried. To accomplish this, create a simple file and specify one database (= TNS entry) per line:

TESTDB1
TESTDB2
TESTDB3

Each entry needs to be a valid TNS entry in your tnsnames.ora. Before any queries are executed, csqlplus will check each entry with tnsping and only execute the query on the valid databases.

Query file

Instead of specifying the query on the command line, you can specify a query file that will be used to execute the query. Create the query file and specify this file using the -f argument instead of specifiying -q. This will also allow you to execute multiple queries or complex PL/SQL procedures. The query file could look like so:

SET LINESIZE 160
SET PAGESIZE 1000

SELECT sysdate FROM dual;
SELECT systimestamp FROM dual;

quit

Important: Do not forget the "quit" command at the end of the file. If you do not specify this command, the script will hang after executing the query on the first database.

Concurrency option

By default, databases are queried serially. When specifying -c, databases can be queried in parallel. This is especially useful when executing large queries or a large number of databases.

Download

Download Here

The source code for the latest version is available on GitHub. Since csqlplus is simply a wrapper script for SQL*Plus, you can download the latest version like so:

$ wget https://raw.github.com/simonkrenger/csqlplus/master/csqlplus.sh
$ chmod +x csqlplus.sh

Credit & License

csqlplus was written by Simon Krenger and is licensed under the GPL.

Oracle, SQL*Plus, SQL*Net are trademarks of Oracle Corporation.