Re: Best way to drop contents of a tablespace?

On Apr 20, 9:58 am, Pat <pat.ca..._at_service-now.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Since you insist up on doing this the hard way you should be using the USER_OBJECTS view and dropping objects in the reverse order of their creation:

select 'drop '||object_type||' '||object_name||';' from user_objects
order by created desc;

Of course this is a simple script and assumes that dependent objects are created after the parent has been created. The errors you experience might be a result of trying to drop these objects in alphabetic order, placing the parents before the children and causing an ORA-02449 error. Further into your drop list child tables are successfully dropped, and so, in subsequent runs, parent tables can be removed since the dependencies no longer exist. To be safe in this regard you should be using the USER_CONSTRAINTS view. A quick and dirty effort follows:

column sort_order noprint
column dct noprint
column dct new_value depend_ct

select count(*) dct
from user_constraints
where constraint_type = 'R';

select 'drop table '||table_name||';', rownum sort_order from user_constraints
where constraint_type = 'R'
union
select 'drop table '||table_name||';', rownum + &depend_ct sort_order from user_tables
order by 2;

There are probably 'slicker' ways to do this with analytics. However, this should give you a better 'handle' on what is wrong withyour current method and how to correct it.

David Fitzjarrell Received on Mon Apr 21 2008 - 08:00:39 CDT