All geek questions in one place

Configure / break an Oracle schema to build CI without catalog fragmentation

I would like a CI build (like Hudson) to set up and destroy the Oracle 11g schema as part of the nightly build / test cycle for a fairly vanilla JSF / JPA application.

The most obvious way to do this is to reset and recreate all the tables. Although this seems pretty standard (at least what the Hibernate / JPA tools will do automatically for you), I had Oracle database administrators who warn me that the Oracle directory will be fragmented after repeated cycles of creating / deleting objects. Ultimately, this will cause performance problems, since the SYSTEM tablespace cannot be defragmented / merged.

My questions:

  • Is fragmentation a real concern, or do you have nothing to worry about in a typical Webapp development environment?
  • If fragmentation is really a concern, is there a better way to tear down and recreate a schema in Oracle than DROP TABLE / CREATE TABLE?

Thanks!

+6
source share
2 answers

Do not trust these database administrators

When using locally managed table spaces (LMT) with at least 10 g or more, this should not be a problem.

And even if it caused fragmentation, I doubt very much that you can measure its impact - especially on the database that is used for CI.

+7
source

I am going to implement the CI build process for my second Oracle project. I don’t think that dropping and re-creating all will do much harm (as indicated above as a_horse_with_no_name). I am glad to hear what you think about extending CI to database objects - too many teams do not.

Another approach may be to restore the database from a recent backup every night (or use the flashback database) and transfer the application from the “production backup” to the current dev state each time CI starts. Thus, the code that will ultimately be applied to production will be tested every night against what is largely identical to production. This has changed a little in thinking, but not too many changes if you are already thinking about CI.

If you want to try the migration approach, I have a tool that I worked on that can help - http://dbgeni.com It is still very much in development, but I developed it using CI and manage database changes with taking into account migrations.

+2
source

Source: https://habr.com/ru/post/902679/

More articles:


All Articles