Re: Best way to drop contents of a tablespace?

From: Pat <pat.casey_at_service-now.com>
Date: Sun, 20 Apr 2008 07:58:29 -0700 (PDT)
Message-ID: <f6ecb38f-e387-466a-9ae4-fc7aaa37f004@d1g2000hsg.googlegroups.com>


On Apr 20, 5:07 am, Frank van Bortel <frank.van.bor..._at_gmail.com> 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. Received on Sun Apr 20 2008 - 09:58:29 CDT

Original text of this message