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.

Need Your Help

intellij idea. what library uses project?

maven intellij-idea libraries

I have Android Maven project which works with Netbeans and Eclipse and compiles in command line itself, but i have some problems with Intellij Idea. After importing the project the IDE complains ab...

view android calendar in titanium

android calendar titanium appcelerator appcelerator-mobile

I'm trying to use "Titanium.Android.Calendar" API to view the device calendar (the native one), I've seen the examples in the website but it doesn't help ..

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.