Re: Best way to drop contents of a tablespace?

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 Received on Sat Apr 19 2008 - 10:57:58 CDT