Best way to drop contents of a tablespace?

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? Received on Sat Apr 19 2008 - 10:06:01 CDT