Re: dropping tables in a tablespace

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Wed, 22 Sep 1999 01:32:30 GMT
Message-ID: <37ef249d.12483983@netnews.worldnet.att.net>

Do the following from SQL*Plus

spool delete_all
select 'drop table ' || table_name

       || ';'
from user_tables
where tablespace_name = 'XXX';
spool off

That should give you a file of DROP commands that you can then execute with:

@delete_all

I assume that none of these tables are partitioned, right? If a partition is in your tablespace, that makes things more complex. You then need to look at user_partitions.

regards,

Jonathan

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are

On Tue, 21 Sep 1999 15:29:50 -0400, Sudarshan Sampath <ssampath_at_scr.siemens.com> wrote:

>Hi folks,
>I am trying to do a fresh import of data into a tablespace. Right now I
>want to
>get rid of all the tables (250 of them) in this tablespace as it I want
>to import
>the tables afresh.
>
>How do I drop all tables in a tablespace? I know how to drop a
>tablespace...
>but that means I would have to create new tablespaces with the right
>parameters
>from the beginning. I want to be able to clear out all the data (tables,
>indexes
>views....etc.) in a tablespace all at once. Is there a command to do
>that?
>
>Thanks,
>
>-Sudarshan
Received on Tue Sep 21 1999 - 20:32:30 CDT