Re: Best way to drop contents of a tablespace?

On Sat, 19 Apr 2008 14:31:08 -0700 (PDT), Pat <pat.casey_at_service-now.com> wrote:

>On Apr 19, 2:14 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>> On Apr 19, 10:59 am, Pat <pat.ca..._at_service-now.com> wrote:
>>
>>
>>
>>
>>
>>
>>
>> > On Apr 19, 8:57 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>>
>> > > On Apr 19, 10:06 am, Pat <pat.ca..._at_service-now.com> 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. It'd be easy to
>> > > > write a script to drop and recreate the tablespace, but the UID I
>> > > > isn't supposed to leave his tablespace, hence I just want to "reset"
>> > > > it by clearing everything out.
>>
>> > > > The good news is there's nothing unusual in the tablespace, just a
>> > > > bunch of tables and indexes e.g. no stored procedures, materialized
>> > > > views, etc.
>>
>> > > > My first, naive approach was:
>>
>> > > > Select table_name from all_tables where owner = <this user> and
>> > > > dropped = no
>>
>> > > > for each table
>> > > > drop table <table>
>>
>> > > > Problem I'm running into is that tablespace in question has 500 or so
>> > > > tables (and probably 1500 or so index in it) and my script will run
>> > > > for a while, happily deleting stuff, until at some point I'll get:
>>
>> > > > ORA-00604: error occurred at recursive SQL level 1
>> > > > ORA-02264: name already used by an existing constraint
>>
>> > > > And my script will abort.
>>
>> > > > If I keep running the script, I can "nibble" away at teh database and
>> > > > drop, say, 100 tables per run and by the 5th or 6th run I'll have
>> > > > dropped 'em all, but that doesn't strike me as a good approach. Plus I
>> > > > want to know what's going on here since I don't like messing with
>> > > > stuff I don't understand.
>>
>> > > > Does anybody know why a drop table would give errors like that? Is
>> > > > this Oracles way of telling me I have open cursors against it or
>> > > > something (maybe from another session)?
>>
>> > > > Alternately, is there a better way to clear out a tablespace w/o
>> > > > actually destroying and recreating it?
>>
>> > > You should probably consider dropping the user cascade then recreating
>> > > the account, which would probably be much less of a pain than your
>> > > current attempt. You should have scripts to create the users to begin
>> > > with, so a ' drop user ... cascade;' shouldn't be an issue.
>>
>> > > David Fitzjarrell
>>
>> > If I'm connecting as that user, can I drop myself? I kind of assumed I
>> > needed to keep the UID intact since that's who I was logged in as?- Hide quoted text -
>>
>> > - Show quoted text -
>>
>> The goal here is to make this as painless as possible. You can't be
>> connected as the user you should be dropping, thus you should be
>> connecting as SYS, SYSTEM or another DBA-privileged account, drop the
>> user cascade then recreate the account. I presumed that fact would be
>> evident from the advice given; obviously I was wrong.
>>
>> David Fitzjarrell
>
>
>Any script I write has to run within an application. Said application
>is given one and only one account with access to only one tablespace.
>If I could count on having a DBA account I'd happily just drop and
>rebuild the tablespace. I don't though. What I have is a limited
>rights account with access to a single tablespace, which I'd like to
>clean out as painlessly and efficiently as possible. Frankly I don't
>care too much about efficiency either, just as long as any approach
>works consistently and correctly.
>
>I know plenty of ways to solve this problem with a more priveledged
>account, but that's precisely the problem I'm trying to avoid. Right
>now, anytime one of the developers needs to reset their test instance,
>they have to call me and I have to log in as a DBA account, drop, and
>rebuild their tablespace. I'd rather give them a piece of automation
>they can run themselves, with their limited rights account, that will
>clear out their tablespace so they can start over. Likewise, I don't
>want to give them dba because with that they could do a lot of harm.

I don't think you are correctly using the term tablespace as it relates to Oracle. I think you mean schema. In my opinion, the only effective way to "clean out" a schema is, as suggested by Mr. Fitzjarrell, is to drop and recreate the user.

Nonetheless, if you wish to drop tables and views for a user, you could probably utilize a simple script such as this:

set heading off
set pagesize 0
set feedback off
spool tablelist.sql
SELECT 'drop table ' || table_name || ';' FROM user_tables ; SELECT 'drop view ' || view_name || ';' FROM user_views ; spool off
@tablelist.sql
exit

HTH,
Arch Received on Sat Apr 19 2008 - 17:26:00 CDT