Freitag, 8. Februar 2013

Cannot drop a user that is currently connected

I hate this message. It constantly broke our CI builds. Here is why and how I fixed it.

The Basic Setup

We use Maven as our build system. In UI projects we start a Jetty server that hosts our application and run a bunch of frontend tests against it. Our application also needs an Oracle database so we setup and tear down this as well. The build phases go like this:

- create database user (== schema in Oracle)
- update database with the latest scripts
- jetty:start (daemon mode)

- failsafe plugin runs tests

- jetty:stop
- drop database user

The Problem

The problem comes with the post-integration-test phase. The jetty:stop command returns immediately while the server is busy shutting down. All the while our drop script tries to "Drop user @username cascade;" and fails with the error that gave this post its title. So our first reaction was to write our own little Jetty plugin that actually waits for a complete server shutdown. This worked, but it made builds unnecessarily slow.

The Solution

So I took another go at our drop script and changed it to this:

With this script in place we could now shut down Jetty and drop the Oracle user in parallel, making our builds that much faster. 

As a bonus I stumbled across another Oracle oddity: Our users have dedicated tablespaces (named "<username>_<tablespacename>"), so we drop them too. Since we already did a cascading drop of the user, we can be sure that those tablespaces are empty. You would think that Oracle recognizes this, but at least on 11gR2 you would be wrong. Calling "drop tablespace @tablespacename including contents" is much slower than "drop tablespace @tablespacename" even if the tablespace is empty. This spares us some more valuable seconds in each build.