Oracle XE: It's Not Your Typical Oracle
Suit vs. Jeans
Try a visualization. Think about Oracle Database. If you are anything like me, you are probably envisioning large, mission-critical applications, darkened server rooms with loud fans and lots of heat, and DBAs sweatily laboring to keep the enterprise running. You might, if you have had the experience, also get a sinking feeling from the installation, configuration, and administration of the beast (in other words, oh no, not all those steps to run through again).
Now think about MySQL. You will probably think of Web applications (particularly the new Web 2.0 everyone talks about), fast and lightweight development, simple clean tools, and even simpler and cleaner installs. (It is just there on most Linux distros these days, and Windows installation is just a double-click away.)
Well—maybe I hit with this and maybe not, but this was certainly my thinking, up until a couple of weeks ago, anyway. MySQL has been adding more and more capabilities as time goes on, such as stored procedures and triggers, but for the enterprise, Oracle still rules the roost. On the other hand, the plethora of new lightweight Web applications out there tend to target MySQL; it is available on just about every hosting service out there, and is just easier to get up and running for your own development purposes.
It is the latter that appears to have been worrying Oracle for some time now. They are aware that the "cool stuff" is happening on MySQL (and to be fair, possibly Firebird or Postgres too). Clearly, Oracle needed to do some re-thinking.
The result of this re-thinking? Oracle XE (Express Edition). When I first heard about XE, my reaction was "Well, this will just be some warmed-over version of Oracle trying to stem the flow of the cool kids to MySQL."
There are two things wrong with this analysis:
- I don't believe their is a "flow" of cool kids from Oracle to MySQL. The cool kids are already using MySQL, and I don't think that many come from Oracle; they simply start out with MySQL and stay there. Oracle, with its licensing, heavy install process, and requirements, is simply not in the running.
- After examining Oracle XE, it is a lot more than some half-hearted, warmed-over TV dinner made up of Oracle 10g leftovers. In fact, as blatant an attack as it might be on MySQL, it is a darned good one! As I will explain in the course of this article, there is a lot to like here. In fact, there is a lot that it does better than MySQL.
Oracle XE vs. Oracle Suit
The Oracle Universal Installer... why did they do it to us?
Actually, to be fair, the universal installer was only part of the problem. Anyone who has installed Oracle 10g (or even worse, 9i) on Unix (particularly Linux) will feel the pain here. Ten-page installation documents with user and group setups, kernel parameter tweaking, compile dependencies, and weird, platform-specific errors. Sure, you can get used to it, you can even get the Linux install fairly repeatable and reliable with a bit of effort and learning, but it's not easy, and it's ugly.
Of course, if you needed Oracle for your business, it was a pain that had to be borne. But if you didn't? Well let's just say it's hard to imagine someone installing Oracle "for fun.".
Windows users had it easier, but it was still far from perfect. The universal installer asks a myriad of questions that you pretty much give the same answers to (at least for a quick "development" install).
In fact, many times in the past I bemoaned the fact that Oracle seemed to ignore the excellent installation options available for Linux—RPMs and/or apt-get are a far easier and quicker way to go.
So, my first pleasant surprise is that Oracle XE finally does this. The universal installer is nowhere to be seen! Packaged versions are available for Windows 32 and many of the popular Linux distros: Debian, Mandriva, Novell, Red Hat, and Ubuntu.
The install was a breeze, not a compatibility library in sight, no DBA group or Oracle user set up. (Actually there is, but it is done for you which, when you think about it, is what computers are supposed to do anyway, right?) The package install takes less than a minute.
Of course, a successful install was only half the battle with Oracle 9i or 10g; afterwards came the configuration step. Administration assistants for several different phases had to be run, to create tablespaces, set up DB sys and user accounts, set up the listener details, and so forth.
Happily, this process has been improved by several orders of magnitude as well. On Linux, you just have to run a script after the package install has completed. The script is mentioned at the end of the package install, so you just have to copy/paste the command. You then have to answer four questions: port to run the http server on, port to run the listener on, select a sys/system password, and whether you want the database to be started automatically on bootup.
A full Linux install takes way less than five minutes. Windows is similarly easy; the questions are asked by the installer .exe and it even skips the port number questions unless something is already using those ports. Windows services for Oracle XE are installed automatically.
If you work on enterprise applications, no matter what technology you use, the chances are good that there is a database of some kind at the core. For quick development projects, this has in the past been MySQL for me.
Tooling for MySQL is the primary reason I use it. Compared with, say, pointbase (shipped with the Sun development tools right now, although being replaced by Derby soon), MySQL has a lot more choice in the tools space. MySQL themselves have a GUI admin tool and query browser which, while basic and a little unintuitive, are ever so much better than nothing. In practice, I tend to use phpmyadmin most of all—this is a separate project that provides a Web-based application that combines most of the functionality of both the admin and query browser. All of these tools have to be downloaded and installed separately to MySQL itself.
The good tooling options mean less time to set up demonstration data, import data from various sources, or design new schemas. Because I dabble in databases only as a way of supporting development, my SQL knowledge is only passable, so having tools that help me define tables and queries quickly is a serious advantage.
The great news is that Oracle XE shines in this area. An excellent Web-based application for administration and query building, as long as an awful lot more, is included in the XE install. This means that the "out of the box" experience for XE is quite a lot better than with MySQL.
On top of this, the Web app is excellent. Frankly, it's much better than I have come to expect from Oracle bundled software. (These are the guys who used to install the database and leave you with SQL/Plus as the primary tool for administration, remember.)
In fact, the Web app bundled with XE includes the following functionality (and a lot more):
- Common system admin functionality, including schema and user creation
- Database monitoring (load levels, resource limits, statistics, top SQL queries, sessions, and so on.)
- Create/Edit tables, sequences, indexes, triggers, views, functions, stored procedures, packages, and the like.
- SQL editor and Query Browser (heavy on the AJAX—more below)
- Dump and Restore database, generate DDL
- Import/Export to Text, Spreadsheet, or XML
- Application builder with example applications ready to install. Again, more below
Important note: Stored procedures and full packages are included in Oracle XE.
As you can see, the bundle includes quite a lot, and useful functionality at that.
The other thing worth mentioning is the integration into the operating system/desktop environment. This is a fairly new thing for Oracle, especially on Linux. Start menu entries include start and stop database, get help, and go to the database home page.
Gotcha: If you need to get back to the home page, the start menu is one way to do so, but the direct URL is http://localhost:8080/apex. (This assumes that you didn't change the default port 8080; if you did, adjust the port number accordingly.)