Installing Oracle XE (Express Edition) on Server

I'll start with a recommendation :

Use a dedicated server as your development database, or at least, don't use it on your development machine.
It'll slow down your development machine and development process.
The XE is simple to install, but it's not MySQL. It is a slightly heavier database.


I know most of you are used to a database installed on your machine (Like most of MySQL users do), but that's not a way to work with Oracle database.
Even not with the smallest one.

You can easily install one Oracle instance on a server machine, and even develop lots of Web 2.0 applications using this one instance, or give each one of the developers a saparate "Database" to work with, like theyr'e used to.

I, personally, recommend one development database for all the developers together, for most cases.
It is easier and safer to manage one database account.
Plus, you'll have bigger amounts of data during the development process, so you can tune your application better and earlier.



How do I install oracle XE?

OK, so we don't have a lot of choices here, as the XE can be installed on linux or Windows machine.

Now, this XE installation was a little bit of surprise for me when I first saw it.

Anyone who is familiar with Oracle database products knows installing these products costs you a lot of time and nerves.

You read installation documents, compile dependencies, and ALWAYS find out that for some weird reason you can't make it run correctly.

Don't worry, almost anything that happened to you, happened to someone else too.
Remember there is an Oracle database in almost any big organization, and trust me ,it was installed successfully in almost any configuration you would could imagine.

So, there is good support and full documentation, but the ease of installation cannot be compared with the small databases that rule the web 2.0 world (MySQL, PostgreSQL), being pre-installed, or easy to install on almost any platform.


And here comes Oracle with some news, and makes the XE, one easy to install application. At last...


LINUX :

As this product is targeting the apt-get generation of developers, this is how you install oracle on debian distributions (Debian, Ubuntu, Kubuntu and so on).

# apt-get install oracle-xe

Or, if you're going to use language that is not western European (Chinese, hebrew etc.) in your data:

# apt-get install oracle-xe-universal

And that's it.

If you are going to use only English, or Western European languages that does not require Unicode, I recommend that you download the first one (the xe-universal) , because in the universalversion all character columns (CHAR, VARCHAR2, CLOB etc.) take double space on the disks (2 bytes for each character instead of 1).


WINDOWS :

As for windows users, it's a bit less convenient. But, we're used to it, aren't we?

First, go to
http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html
with your favorite browser.

Download the installer (exe file). (Remeber to download the universal version if you intend to be international).

Double-click on it.

Click Next, Next, Next etc. and press some kind of an "I agree" disclaimer (Linux users seem to avoid this stupid process), and you're done.


Hey, that was almost too simple... is it really Oracle?
Yes it is. And it is one powerful database...

And now you have your own instance of oracle on your machine.

I hope for you it is a dedicated machine.

I'll say it again, because I know it's tempting, as the installation is so simple : it'll slow down any development machine you'll work with, specially if it's a windows machine.

That's it for today.

Next post :
Finally, getting to work : Connecting rails to oracle, and handling some very annoying problems.

Why Oracle On Rails?

Well, the answer is quite simple.

First, I will admit, I never tried Postgre SQL database, and, that's why it is not included in this post.
As for SQL-Server, I believe that there are lots of people more qualified than me for writing about it. So, I'll let them write their own blog.
The main problem with SQL-Server for Rails development, is of course the fact it requires the Windows OS for it to run, even in the free (Express) version. And Windows isn't free yet.

The main database for most Rails applications on the Web is the MySQL database, for the following reasons:

1. Installation : It is very simple to install on almost any Operating system, and on most of the main Linux distributions, MySQL even comes pre-installed.
2. First use : Once you install it, no adjustment has to be done (From the database side) for RAILS to access the database.
3. Costs : MySQL is an open-source product, is free, and will always remain free. We like to support this kind of projects, anytime we can.
4. Weight : The development in a typical rails project is using a database on each developer's machine, and MySQL is a fairly small application which doesn't consume too much memory and CPU. Very convinient.
   
5. Widely used : The main reason - each and every one of the Rails books, PDF's and web tutorials use MySQL. why should I be different?

But, MySQL has some limitations, that most of us don't even consider when we start developing.
Here are some of them:

1. Data size limit : MySQL has a data storage limitation. Did you know it? MySQL is limited to 4GB of data per table, due to it's 32-bit pointers.
   So, if you expect, or even suspect that your application might grow to a table with size larger than that, MySQL is not the answer.
   There are workarounds for that (http://jeremy.zawodny.com/blog/archives/000796.html), but in practice, MySQL becomes slow even in smaller tables due to :
   
2. Complex selects : MySQL has a pretty naive optimizer. For example, if there are 5 indexes on one table, even if it's much more efficient to use 2 or 3 of them, MySQL will always use one, and only one. he's not any smarter than that.
   This is just a small example, and even if you didn't understand a word of it, trust me :
   Even if it works fast for you on a developer machine, when your database grows you don't want to use MySQL for complex selects. Use something else.

Is that it?
Well, yes, that's it.

This are the cases you souldn't use MySQL:

• Database that is expected or suspected to grow large.
• Application that requires (or will require) complex selects or reports.
  Even one select should be considered. Remember : those selects are very hard (and sometimes impossible) to tune on MySQL. Be warned.
  

If none of these cases is true for your application (Your application uses a small database in a very simple way, with no complex select, no reports, and so on), your BEST choice is MySQL. Have fun with it. It's much simpler.

If any of these cases is true. You are in big trouble. You're gonna have to learn using another database, which doesn't appear in almost any book about rails.

Let's talk about Oracle database (for PostgreSQL or SQL-Server, refer to the beginning of the post):

So, let's test it with the set of reasons that led us to choose MySQL in the first place:

1. Installation : Oracle has a product called Oracle database XE (Express edition). It is a fully functional oracle database, with few limitations :
   
   • 4GB of data.
     
   • 1 CPU.
   It's easy to install the XE on windows and linux, but you need to have admin permissions. I never tried to install it on unix machine, and actually, I see no reason for it...
   
2. First use : Once installed, a user has to be created for the rails application to use, and has to be given the right permissions. Only after you've done that, rails can access the database.
   More safe, less convinient.
   
3. Costs : Well, you'll be surprised. Oracle XE's licence allows you to do anything you want using it, for both commercial or non-commercial uses, as long as you don't break any of the limitations mentioned in Installation.
   Practically, up to the 4GB limit it's just as free as MySQL.
   After the 4GB limit, well, MySQL is just not a player in this league.
4. Weight : This is the main problem with Oracle XE compared with MySQL. It's much heavier, and you'll want at least 512MB RAM on the machine running it.
   So installing it on every developer machine wouldn't be that wise.
   But hey, who needs it, when you can create as many database users as you wish on you unlimited Oracle XE instance?
   Just create one free database (XE on linux) and create a user for each environment, or, if you want to stick to the Database For Each Developer concept (Why woul you do that?) you can create a user for each developer. Aha, just like the old MySQL days...
   
5. Widely used : So here is the common problem. Oracle is not widely used and documented inside the Rails community.
   And here are some good news : Oracle is now starting, slowly, to understand the potential of the Rails community, and starts guiding the community on how to use Rails with Oracle.
   But, in a larger perspective, in the real world, Oracle database is used much more than Rails and MySQL Togegher. It means that it's more documented and supported, and very reliable and powerful.

So, conclusion for this post: You can work,for free, with a much more powerful, and reliable database, that will let your application scale smoothly. Actually, when your database grows, all you have to do is buy a licence to a bigger instance of Oracle database.

And hey, if you have a Rails web 2.0 app that holds more than 4GB of user content in your database, man, your traffic rocks - you can afford yourself a licence of a good reliable database.
Or, at least, you can not afford yourself a slow database or a non-reliable one.

Next posts :
Installing Oracle XE on a server (Debian or Windows), Connecting to it from a development machine, and fixing the most common problem of rails and oracle (the date columns).

If you have any questions or cases regarding Oracle and rails don't hesistate sending them to oraclerails@gmail.com. I'll be happy to help you and post a note about the problem and solvement in this blog.