Setup/tear down Oracle schema for CI build without fragmenting catalog
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?
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.