Re: Best way to drop contents of a tablespace?

From: joel garry <>
Date: Mon, 21 Apr 2008 10:45:11 -0700 (PDT)
Message-ID: <>

On Apr 20, 7:58 am, Pat <> wrote:
> On Apr 20, 5:07 am, Frank van Bortel <>
> wrote:
> > Pat wrote:
> > > I need to write a script that will log into a server as a user and
> > > delete the contents of that user's default tablespace.
> > You cannot - you do not seem to have a grasp of how tablespace(s)
> > and users come together.
> > Unless you logon as a super user (as already indicated by others)
> > you cannot do what you describe you want to do.
> > Any tablespace may contain objects of another user - so you
> > cannot delete the contents of a tablespace (unless you own it)
> > Unless you describe it as I want to drop all user objects.
> > Replace the word objects by tables, or what ever.
> > What is the purpose of all this? Why do you think there
> > is a need to remove all user data?
> > Regards,
> > FvB
> 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

Some things to look at:

Are you using constraints to define not nulls? Perhaps you need to disable constraints before dropping? Are you SURE everything is in the tablespace, and not, say, in SYSTEM? Probably won't do anything, but you might consider a coalesc before reloading.


-- is bogus.
"Robots rules of order.  Never point a robot towards the sun.  Oh, my
eyes." - Firesign Theater.
Received on Mon Apr 21 2008 - 12:45:11 CDT

Original text of this message