If you work with commercial databases Oracle or Microsoft SQLServer, there’s a couple of free tools you ought to know about. For Oracle admins, users, and developers, there’s TOra, the Toolkit for Oracle. By our estimation, it’s as good as TOAD and free is a VERY good price. TOra runs on Linux, Solaris, and Windows. For commercial use, there is a license fee for the Windows product, but at $100 it is a bargain compared to TOAD, SQLNavigator, and the like. TOra can also connect to MySQL databases.

In part one of this article, we will install it on a workstation running Red Hat Linux. But first, as a special treat, we will install the Oracle client libs and utilities from a download from the Oracle website. Because you don’t think we’d give up SQLPlus, do you? Hell no! Give it to me on the command line, plain and to the point. In part two of this article, we will install SQSH, a free isql-like command line client for MS SQLServer, and the libraries required to build it. So stay tuned for radical cross platform fun.

Installing Oracle Tools

First, download oracle for GNU/Linux from the Oracle website. Follow the instructions on the site for uncompressing the three iso images. Then create an oracle user and an oracle home directory, I go with the classic oracle:dba and /opt/oracle. Now su to oracle, cd to the Disk1 directory, run the runInstaller program and let the fun begin. Note – on my Red Hat 7.3 system, I had to issue an xhost command as the user who started X on the system in order to successfully bind to display 0:0. I just typed “xhost localhost”.

Choose the location, and enter in the oracle user and group that you created earlier. You’ll need root privileges to run a couple of shell scripts that the installer prompts you to run. For our purposes, just install the Oracle9i Client. I go for the full Administrator package. It’s pretty huge, almost 900 MB. The Oracle Net Configuration Assistant will help you create an initial tnsnames file. Should you need to add more databases by hand, the location of the file (using our installation options) is /opt/oracle/OraHome1/network/admin/tnsnames.ora. Here is what an entry looks like:

DB.DOMAIN.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fatman.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db.domain.com)
)
)

Installing TOra

Now that the Oracle client tools are installed, we have the necessary goodies on our system to run TOra. You can install it from RPM or source. To build it from source, you must export ORACLE_HOME (in our case /opt/oracle/OraHome1) and possibly the location of the QT libraries if the configure script can’t find them. Which brings us to the point that TOra is a KDE-compatible app and does require the QT libraries. Because we are lazy and running Red Hat, we are going to use the RPM.

[root@srv-3 tools]# rpm -i tora-1.2.4-1oracle9.i686.rpm

Set up the following environmental variable in your profile:
ORACLE_HOME=/opt/oracle/OraHome1
export ORACLE_HOME
And read in the new variable.

[usr-3@srv-3 usr-3]$ . ~/.bash_profile

Now we are all set to run TOra, which is installed as /usr/X11R6/bin/tora on our system.

I leave as an exercise to the reader the fun of connecting to the database of your choice and using the many tools provided by TOra. (If you are inferring by my failure to provide screenshots that my database privileges have been revoked, you are sadly correct. However many fine screenshots are available here.)
Stay tuned for SQSH, CLI access to MS SQLServer from Linux.