Re: Best way to drop contents of a tablespace?

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