Wednesday, April 12, 2006

Oracle XE and Ubuntu

WOW. I just installed Oracle XE on my Ubuntu machine. I absolutely cannot believe how easy it was. This is - honest to goodness - all I did.
  1. Download oracle-xe_10.2.0.1-1.0_i386.deb
  2. su - root
  3. dpkg -i oracle-xe_10.2.0.1-1.0_i386.deb
    It ran for maybe thirty seconds - so short, I was certain there had been an error!
  4. /etc/init.d/oracle-xe configure (it told me to do that)
  5. pointed Firefox at (it requested that, too)
  6. Started using the database (plus its included Application Express).
The entire installation took less than five minutes. Unbelievable! "This is Oracle?"

The only glitches I've gotten so far were when using Python's cx_Oracle against XE, and I've puzzled them out. (I don't know whether other people will get these glitches; they could have resulted from some residue of the full-fledged 10.2 Oracle that was on the machine before.)
  • import cx_Oracle gave ImportError: cannot open shared object file: No such file or directory until I set LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/
  • conn = cx_Oracle.Connection('scott/tiger@xe') gave RuntimeError: Unable to acquire Oracle environment handle until I set ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

[EDIT Oct. 9, 2007: Configuring cx_Oracle with Oracle XE has turned out to be harder than expected. See my new blog post.]

[EDIT Mar. 6, 2008: Great instructions for installing straight from Oracle's repository with apt-get here]


Anonymous said...

Hello Catherine:
How is the performance of XE? Is this faster than SQLITE, for example?


Unknown said...

Hi, Eduardo! Hmm... sqlite vs. Oracle XE... OK, here are my totally off-the-cuff impressions from what I've done with them.

If an sqlite database is less than about 1 GB (and that's a low-fat 1 GB, understand), its performance is surprisingly close to Oracle's - probably still slower, but not by much. If the database is bigger than that, or multiple processes are performing DML at the same time, then sqlite falls way behind.

Oracle XE is honest-to-goodness, real Oracle, same optimizer and everything, so its speed should be the same as Oracle Enterprise or Standard Editions. XE simply doesn't have the fancy-footwork options that EE/SE have. Sure, some of those fancy-footwork options can speed up certain queries, but only if you use them, and you have to be pretty seriously into it to use them.

If raw query speed is your main concern, Oracle would be the choice. OTOH, even XE is still a lot more work than sqlite. For instance, backing up and restoring Oracle is still a complex and scary operation, whereas a sqlite database is an ordinary file that you make a copy of the same as any other file.

Anyway, there are so many differences between them that it outlining their relative advantages would be a long article, so I'd better stop now before I accidentally write one.

Anonymous said...

You're not alone. I had to set LD_LIBRARY_PATH and ORACLE_HOME as well.

Oh, and it's considered bad form these days to 'su - root'. In Ubuntu you'll be set up in /etc/sudoers (or whatever the file is called) so you can do;

$ sudo dpkg -i ...

Or, as I mentioned on my blog [1] just add the Oracle repository to your /etc/apt/sources.list file and do;

$ sudo apt-get install oracle-xe


Anonymous said...

Thanks, very helpful in my desperate hour of need.

Chili Joe said...

Is the scott/tiger schema available by default in XE/Ubuntu?

Unknown said...

Thanks, Anonymous!

Joe, I'm pretty sure that XE installs with the "HR" schema instead of Scott. Bo-ring! I'm not positive, but I think I had to create scott/tiger myself. No database of mine is going to be without scott/tiger!

(Er, but it will be locked on production systems.)

(Article on replacement of SCOTT)

Hmm, I wonder if anyone out there has published a script to generate scott on a tragically impersonal database that lacks it?

keeb said...

Tsk Tsk Naughty Naughty.. using su - root instead of sudo!

It appears the cx_Oracle requires a server installation on the machine, as the client binaries don't seem to be sufficient. I am still getting the RuntimeError: Unable to acquire Oracle environment handle eventhough my $ORACLE_HOME is set properly.

Anonymous said...

Have you been able to view and edit stored procedures from the APEX interface ?? cant get that...