Re: Best way to drop contents of a tablespace?

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 Received on Sat Apr 19 2008 - 16:14:15 CDT