A "Cluster SQL*Plus" to manage multiple databases in large environments
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.
csqlplus is a simple wrapper script to call
sqlplus for all databases specified in a file.
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:
tnspingare in your current
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
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
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:
Each entry needs to be a valid TNS entry in your tnsnames.ora. Before any queries are executed,
TESTDB1 TESTDB2 TESTDB3
csqlpluswill check each entry with
tnspingand only execute the query on the valid databases.
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:
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.
SET LINESIZE 160 SET PAGESIZE 1000 SELECT sysdate FROM dual; SELECT systimestamp FROM dual; quit
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.
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
Oracle, SQL*Plus, SQL*Net are trademarks of Oracle Corporation.