Getting started with Oracle Database

Question

I've been assigned the task of making our Java web application work with Oracle Database. I am not sure where to start. I was hoping for an experience similar to working with MySQL or PostgreSQL, but no such luck.

The answer to this question will be list of steps for installing and deploying an Oracle Database for development use, basic operations such as starting and stoping, creating and dropping schemas, and perhaps even JDBC connection parameters.

A little bit of background about my project (although ideally the answer to this question will be as general purpose as possible and not be tied to the specifics of my environment):

1. Java (and Scala) web apps deployed in Tomcat
2. Hibernate (currently connected to PostgreSQL and MySQL)
3. Developer workstations run OS X; production deployments are to Red Hat Enterprise Linux

Answer 1

If your database is relatively lightweight and doesn't heavily rely on Oracle-specific features, I would recommend you to use XE for development. In the other case, it might make more sense to dedicate a separate box for running Oracle for development.

As for the basic steps you mentioned, the later versions of oracle come with a rather nice web-frontend(which looks like that) that can handle all of these maintenance tasks. If you would need something better and more responsive, go for Oracle SQL Developer, which is a desktop application somewhat similar to pgAdmin.

Installation is also much easier with the newer versions of Oracle (10g and up), so you should not have any problems with that.

Edit: On memory optimization (if you insist on having an Oracle instance on your workstation and your database is relatively light), check this thread since it provides a multitude of good hints.

Answer 2

I have not found a fundamental difference between MySQL and Oracle from a developer's point of view. It's just tables, joins, and SQL. If your database gets enormous or needs insane throughput then yes, it matters a lot. But by then you'll need a real DBA.

1. Download SQLDeveloper from Oracle. It is free. It will let you perform database maintenance, run SQL statements interactively, etc.

2. Read up on "connection pooling" and "oracle". There probably some good threads here on SO. You'll want this as it will make your servets run efficiently.

3. Read up on JNDI database connections. This is simply a way to specify the database connection in your Tomcat configuration so your applications don't have to know anything about the database. You'll be able to move your war files from test to QA to prod with less difficulty.

4. Hibernate is the trickiest thing of the bunch! Keep it simple! I highly recommend you reverse engineer your model classes from the database and never manually modify them. You can also generate your DAOs (recommended) but you will modify those.

5. Use standard proper database design as you'll have to play fewer games with Hibernate. For example, always put a numeric PK on each table, and use an Oracle sequence to populate it. Always use FKs when appropriate. Try to normalize your data to a practical extent.

6. Use Ant or Maven for your builds. Don't do anything by hand.

7. Use SVN or similar.

That's the biggies for now.

Answer 3

Firstly, don't think about Oracle on OS X. You'll need Linux (or Windows), maybe on a virtual machine/

Then decide whether you'll be using Oracle 10g or 11g. 10g has a free Express Edition which is relatively easy to install on Linux or Windows. However, if you want 11g or bug fixes to 10g or some of the extra options (compression, partitioning...) you'll need a fully licenced version of Oracle. Depends on your load, but you can have one physical machine which is licenced for Oracle, with multiple VMs (one for each developer).

XE has a single database instance that the installer will configure to start up when you start the machine. If you are just using the VM for the database, having the database startup and shutdown with the machine is the easiest way to go.