Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: dropping a large number of tables?

Re: dropping a large number of tables?

From: John Durst <jdurst_at_sky.net>
Date: 1998/10/20
Message-ID: <362D5B44.47DFE538@sky.net>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US