Re: Best way to drop contents of a tablespace?

From: Frank van Bortel <>
Date: Mon, 21 Apr 2008 21:16:49 +0200
Message-ID: <68e36$480ce821$524b5c40$>

Pat wrote:

> Sounds like I'm getting my terms wrong here so I'll try to explain
> what I'm after again.
> My company sells an application that runs on a variety of databases,
> one of which is Oracle.
> In order to test on Oracle, I've created a number of tablespaces and
> test accounts for the test group.
> Each of them has a UID/Password pair that logs them in and sets their
> default tablespace.
> For example, a user named "bob" with a password of "taco" logs into a
> tablespace called BOB_TBS.
> They aren't really logging on with sqlplus or anything though, they're
> just using those credentials for a java application. That application,
> in turn, logs on with their credentials and they get dropped into
> "their" tablespace. Once their, the application runs over the tables
> there and does its thing.
> They're a test group, so every few days they get a brand new version
> of the application and need to test it. Right now, they call me and
> say "hey, we've got a new build".
> Then I log in as a DBA and drop BOB_TBS.
> Then I create a new tablespace named BOB_TBS.
> Now, since bob's UID still defaults to BOB_TBS, he can test again with
> an "emtpy" tablespace and the app will go through its first boot
> sequence.
> Problem is, while this worked out ok if there was only one or two guys
> testing on Oracle and we didn't get builds very often, we're in a test
> cycle right now with a bunch of guys testing new Oracle builds pretty
> much daily. Which means I'm spending a whole lot of my time dropping
> and recreating tablespaces (not to mention all that IO thrash on the
> test server, slowing everybody else down).
> So my idea was to give the testers something they could run with their
> own user accounts that would clean their account. The way I've been
> doing it (while logged in as a dba)is by dropping and restoring their
> tablespace (since they way I set those accounts up, the tablespace is
> used by only a single user).
> As others have mentioned, I really only need to get rid of that user's
> contents, so I'm looking for a script to do that. My first approach
> was to select all tables owned by the current user_id and iteratively
> drop them. I started getting errors I didn't quite understand in this
> context though (see original post) which, in my experience, usually
> means that I'm not doing something wrong and/or forgetting an
> important step.

Why on earth give each tester a separate tablespace? Why not just create one (or user one of the defaults, e.g. USERS), and drop the user, and recreate it? That is the fastest way to really clear out everything.

Somewhat vaguely described, Joel already warned about objects in the SYSTEM tablespace: all source code ends up there - not in the default tablespace... And then there are other objects, like (private) synonyms and such... All things a "drop user cascade" would end.

And your errors are probably due to Oracle, warning you about integrity you are about to mess up. In that case, drop the table constraints first (if you still do not want to drop the user). As you are to remove all objects of a user, you couldn't care less.

Still, drop user cascade would be the fastest option, as indicated by several die-hards. It's up to you.

FvB Received on Mon Apr 21 2008 - 14:16:49 CDT

Original text of this message