Re: Best way to drop contents of a tablespace?

On Apr 21, 12:16 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> 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.

That's a good point, but I'd give each user a tablespace if I wanted to, say, keep a transportable ts or rman backup to make the "restore a developer's tablespace" procedure less painful. I agree, a drop user script would make more sense (which is how I do it now, but it does require dba privs, which is probably a good thing with all the ddl involved. The OP had mentioned "all the I/O involved," but of course it may be worse dropping and recreating everything slow-by-slow.)

This is a DBA function, no doubt about that.

>
> 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.

I was purposefully vague (as I don't have time to get into all the possibilities), but the OP did mention this is a java app, so we're not sure yet whether that winds up here or elsewhere.

>
> 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.

And when the other developers yell "hey, I was in the middle of..." :-)

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

DBA to a testing group does have some speciality. Personally, I've tended to be pretty liberal for developers, less so for testers, much less so for integration testing, and approach hellishness for production. Sometimes I get crazy when I'm doing soup-to-nuts by myself :-)

jg

--
@home.com is bogus.
"NO HEADLINE IN STORY" - headline in business section of online paper.