Re: dropping a large number of tables?

I'm not sure whether you really mean your tablespace or your schema, so I will show you how to do both:

(1) Your tablespace:

set pagesize 0 feedback off
spool dropem.sql
select 'drop table '||owner||'.'||table_name||';' from dba_tables where table_name like 'mytable\_%' escape '\' and tablespace_name = 'mytablespace';
spool off
@dropem

(2) Your schema:

set pagesize 0 feedback off
spool dropem.sql
select 'drop table '||table_name||';' from user_tables where table_name like 'mytable\_%' escape '\'; spool off
@dropem

bigdavemaw_at_my-dejanews.com wrote:

> I have a large number of tables that are all start with "mytable_".
> i.e. mytable_1, mytable_2, ...
>
> What would be the best way to drop all tables in my table space that start
> with mytable_ ?
>
> I know there must be a system table that lists all tables for a tablespace,
> but don't know what it is.
>
> Any ideas?
> Thanks,
> Dave
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Oct 20 1998 - 00:00:00 CDT