Ubuntu, Oracle XE, and SQLPLUS

In the past for local development, I have used MS SQL Server running in a VMware windows instance, but that got to be too burdensome and consumed to much of my 2GB of RAM (who would have thought that 10 years ago when I was playing Star Craft on a desktop with 32MB of RAM). Anyways, on a recent business trip with a co-worker (Matt White) who also runs ubuntu, he brought to my attention Oracle XE and how easy it was to install via apt-get and how small a footprint it was considering it's a database and it's Oracle.

I have been very impressed so far and would highly recommend it for linux users wanting a local database. Again, not only is it easy to install via apt-get once you add the repos, but I really don't notice it consuming too many resources.

Two hints I would like to self document more than anything is after installation the name of the SID is XE. You don't specify it during installation, but that is what it is. So my connection string in jboss looks like this:

jdbc:oracle:thin:@localhost:1521:XE

The second hint I wanted to self document is how to get sqlplus working. Personally, I'm often times too lazy to write straight SQL to manipulate data manually. Not only that but it's not a real good use of my time. But after this weekend I got familar with it again due to a lack of a good GUI tool like Oracle SQL Developer at one of our production sites. I was basically forced to use sqlplus to change a few values. The one huge benefit it has, is you don't have to wait on some slow GUI tool to load. So locally I now have, Oracle SQL Developer for extended use, got the SQL Query Plugin in Idea to use when writing code, and now sqlplus. So when I am impatient and I don't have Idea up, I plan on using sqlplus.

It doesn't work right out of the box. You have to set ORACLE_HOME and add it's bin directory in PATH and also set the ORACLE_SID.

I added the following to my home's .bashrc file:

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_SID=XE
export PATH=$ORACLE_HOME/bin:$PATH

After that reload the .bashrc file by running . .bashrc and then run sqlplus.