Re: Best way to drop contents of a tablespace?

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. Received on Sat Apr 19 2008 - 16:31:08 CDT