Home -> Usenet -> c.d.o.server -> Re: Best way to drop contents of a tablespace?

Re: Best way to drop contents of a tablespace?

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: Sun, 20 Apr 2008 10:31:22 +1000
Message-ID: <fue2sd$seg$1@registered.motzarella.org> 





Arch wrote,on my timestamp of 20/04/2008 8:26 AM:

> On Sat, 19 Apr 2008 14:31:08 -0700 (PDT), Pat
> <pat.casey_at_service-now.com> wrote:
> 
>> 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.
> 
> I don't think you are correctly using the term tablespace as it
> relates to Oracle. I think you mean schema. In my opinion, the only
> effective way to "clean out" a schema is, as suggested by Mr.
> Fitzjarrell, is to drop and recreate the user.
> 
> Nonetheless, if you wish to drop tables and views for a user, you
> could probably utilize a simple script such as this:
> 
> set heading off
> set pagesize 0
> set feedback off
> spool tablelist.sql
> SELECT 'drop table ' || table_name || ';' FROM user_tables ;
> SELECT 'drop view ' || view_name || ';' FROM user_views ;
> spool off
> @tablelist.sql
> exit
> 
> HTH,
> Arch






Just in case tablespace is what was needed,
this script can be modified to go to


user_segments instead of user_tables


and user_views.  Something along these lines
(test it first!):


SELECT 'drop '||segment_type||' '||segment_name||';'
FROM user_segments


WHERE tablespace_name = upper('<tablespace_name_wanted')
AND object_type != 'INDEX';
Received on Sat Apr 19 2008 - 19:31:22 CDT












Original text of this message