6

I would like to have a CI build (e.g., Hudson) set up and tear down an Oracle 11g schema as part of a nightly build/test cycle for a fairly vanilla JSF/JPA application.

The most obvious way to do this is by dropping and re-creating all tables. While this feels fairly standard (at least, that's what the Hibernate/JPA tools would do automatically for you), I've had Oracle DBAs warn me that the Oracle catalog will get fragmented after repeated object create/drop cycles. Eventually this will cause performance problems because the SYSTEM tablespace cannot be defragmented/coalesced.

My questions are:

  • is fragmentation a genuine concern, or is not something you need to worry about in a typical webapp development environment?
  • if fragmentation really is a concern, is there a better way to tear down and recreate a schema in Oracle than DROP TABLE/CREATE TABLE?

Thanks!

wrschneider
  • 17,913
  • 16
  • 96
  • 176

2 Answers2

7

Don't believe those DBAs

At least with 10g and above when using locally managed tablespaces (LMT) this should not be a problem.

And even if that did cause any fragmentation I very much doubt that you could measure its impact - especially on a database that is used for CI.

  • 1
    LMT's on their own ought to eliminate whatever concern someone had with fragmentation assuming the `SYSTEM` tablespace is locally managed. It's not obvious to me that ASM adds anything. And ASM tends to be a much more politically and technically contentious technology since it's all about letting DBAs handle more storage management tasks which means moving that responsibility away from the groups that do it today. LMT's, on the other hand, are a brain-dead obviously superior replacement for old dictionary managed tablespaces (DMT). – Justin Cave Nov 29 '11 at 20:13
  • @JustinCave: thanks for the hint. I removed the comment about ASM (wasn't that sure anyway) –  Nov 29 '11 at 21:22
2

I am in the process of putting a CI build process in place for my 2nd Oracle project. I don't think dropping and recreating everything will do any harm (as a_horse_with_no_name stated above). I am glad to hear you are thinking of extending CI to the database objects - too many teams don't.

A different approach could be to restore the database from a recent backup each night (or use flashback database) and migrate your application from 'production backup' to current dev state on each CI run. In that way, the code that is eventually going to be applied to production will be tested each night against something that is largely identical to production. It is a bit of a change in thinking, but not too much of a change if you are already thinking of CI.

If you fancy trying the migration approach, I have a tool I have been working on that may help - http://dbgeni.com It is still very much under development, but I have designed it with CI and managing database changes with migrations in mind.

Stephen ODonnell
  • 4,441
  • 17
  • 19