Oracle XE: It's Not Your Typical Oracle

Compatibility

If an enterprise application environment only supports one database, it's usually a safe assumption that the one database is Oracle (unless it comes from Microsoft, perhaps). In the testing I have done, Oracle XE works everywhere I would use Oracle. There are some features that are missing, but these are not highly used features and certainly are not cross-database features. For example, there is no provision for writing stored procedures using Java in Oracle XE, but that is a feature that is not at all common outside of Oracle's database anyway.

I have tested Oracle XE with all of the application environments I am using at present, and it works across the board. It is fairly straightforward to set up and use with Ruby on Rails, and works easily with NetBeans and Java Studio Creator, as well as JDeveloper, naturally. Even the persistence options I tried in Eclipse worked with no problem. This looks, feels, smells, and tastes like the full Oracle as long as you don't try and use the enterprise features.

This is quite an advantage for XE over MySQL. Certainly within the company I work for, and with our customers in particular, Oracle is king. Any application we write that uses a database has to be compatible with Oracle. And, if you write an application using XE, I think it is a pretty safe bet that it will work with full Oracle without much—if any—work.

Gotchas

These are just some general points that might save you a bit of time and head scratching if you decide to give Oracle XE a go:

If you are coming from a MySQL background, Oracle works a little differently to what you might be used to. Oracle has several identifying pieces of information for a server: the host name, the port on which the listener is running, the SID, and the Service Name.

When I installed XE and started trying to use it, I did not know what the SID or Service Name for the database were. All of the various external applications need to know this to allow the drivers to connect.

Also, there are two common types of driver for Oracle, the Java native (thin) driver (which is slightly faster the last time I checked) and the OCI driver used by C, PHP, Ruby, and other languages. The Java thin driver hooks up directly using host, port, and SID information presented in a JDBC URL. The OCI driver goes through a mechanism based around looking up the details in a tnsnames.ora file.

For the default install, the SID, Service Name, and TNS NAMES entry are all simply XE.

If you use Rails, and want to connect it to Oracle XE, first install the Ruby OCI drivers. Details on these can be found in the link above. After the installation edit your database.yml file and use the details:

adapter: oci
host: XE

You also will need a valid user name and password for the database, naturally.

Secondly, do not try and install Oracle XE on a machine with a full Oracle installation on it. It may choke on the install because the full Oracle processes are already sitting on the ports it needs. Also, even if the full Oracle installation is not running when you install XE, we saw an instance where the full Oracle installation was disabled by the XE installation.

Finally (and this is a detail you may or may not need to know, but I include it in case it is useful), Oracle XE, like full Oracle, has more than just an on/off state for the database. In fact, there is a process called the oracle TNS listener that runs and can be used to start and stop the databases.

To use the Start Database option in the start menu on Linux, I found it was necessary to already have the TNS listener running; otherwise, the Start Database operation failed. This means that you cannot remove the service completely and just start and stop it from the menu. If this confuses you, please don't worry about it; however, if you (like me) like to trim down the number of services run automatically during boot, be aware of this.

The Catch?

Okay, so there has to be a catch, right?

And there are some, given the space at which this offering is targeted, I don't believe any of them are prohibitive to using Oracle XE.

First, let's start with the good:

It's free to download and develop on

It's free to deploy (including distribution, as long as you make the other party aware of the licensing details)

It is the first really simple installation on either Windows or Linux that I have ever seen Oracle do

The included tools are superb and very modern and AJAXy

However, there are limitations:

Although you can install it on a multiple CPU machine, and it will run on one, it will only use one CPU on that machine

It will only use a maximum 1 gigabyte of memory (again, it will run on machines with more memory; it just won't use it)

It has a database size limit of 4 gigabytes for user data (this is all available to the user; system data is counted outside of that 4 gig limit)

These limitations sound pretty serious, until you remember one thing. In the all important SMC (Small and Medium Companies) PC development space, the most widely used database is either Microsoft Access, or the MSDE (Microsoft Database Engine). Both have an upper limit of only 2 gigabytes of data storage, and it doesn't stop them being used all over the place.

In fact, for development work, none of these limitations are a problem, and even for small to medium applications, they should not prove limiting. You will not be able to run a massive multi-user enterprise system for sure, but then that's where the big Oracle offerings come in. Certainly, I can see developing such a system using Oracle XE, and then deploying onto a bigger server when you need the size (at which time, a for-pay version with support is probably a good idea anyway).

Also, I actually find the limits a welcome addition in a strange kind of way. In the past, Oracle has displayed a bad habit of systematically taking over any machine on which it is installed, becoming an enormous resource hog. I am sure that a good DBA could prevent this, but I am just a developer who wants to write something quickly, and in my experience, Oracle has always been a bit of a resource hog.

This is the first Oracle database I have installed, with the default settings, That has not taken over my development machine. In fact, it is behaving as a model citizen on my laptop, living in harmony with all of the other applications and development environments I run on it. In fact, I have been running Oracle XE constantly on my laptop for well over a week (I suspend the laptop in between uses rather than rebooting), and Oracle is still using only 6.6% of the memory, even though I have been hitting it to write this article and try it out with a number of different development environments.

Other important limitations worth mentioning are that XE is only available for 32-bit Windows and 32-bit Linux right now. I hope that Oracle will release it for Mac, and in 64-bit flavors for both Windows and Linux. If Oracle really wants to go after the hip crowd, someone needs to tell them that Macs and 64-bit chips feature pretty heavily in that crowd.

Conclusion

As I mentioned at the start of this article, I came into using Oracle XE with some pretty heavy pre-conceived opinions. In the past, Oracle has proven itself to be powerful and competent but also heavy, extremely tricky to install, and basically kind of "square." I thought XE would be a half-hearted attempt to make Oracle cool and relevant to the new Web 2.0 crowd. In fact, I find myself delighted to be totally wrong in my pre-conceived ideas. Oracle XE is an extremely good product, and so "cool" it's kind of hard for me to believe it came from Oracle.

So, kudos to the Oracle guys. Now, please take some of the lessons learned from this and apply them to the "real" Oracle. If nothing else, XE proves that installing, configuring, and administering Oracle does not have to be like performing heart surgery on yourself.

Just one more thing before I go. If you do decide to grab Oracle XE (and I hope you do; there is nothing to lose by trying it out for yourself except for a little time, and maybe a pre-conceived notion or two), do yourself a favor and grab Oracle's SQL Developer at the same time. This is a pure Java application, based on a kind of cut-down version of JDeveloper. It is free to download and use, and it fits hand in hand with XE, giving you a desktop application that you can use to configure and develop in. I may cover Oracle's SQL Developer in a future article; in the meantime, just grab it and give it a try. It's a bit more advanced than the Web tools provided with Oracle XE, and hence might be useful as you start to get more ambitious.

About the Author

Dick Wall is a Principal Systems Engineer for NewEnergy Associates, A Siemens Company based in Atlanta, GA that provides energy IT and consulting solutions for decision support and energy operations. He can be reached for comment on this and other matters at dick.wall@newenergyassoc.com. He also co-hosts The Java Posse, a podcast devoted to Java news and the Java community. It can be found at http://javaposse.com.