Re: dropping a large number of tables?

Alternatively, within sqlplus session

spool drop_tab.sql
select 'drop table '|| table_name ||';'
  from user_tables
 where table_name like 'MYTABLE%'
   and tablespace_name = 'DESIRED_TABLESPACE'; spool off

Then edit this file to remove sql query and run it from within sqlplus

HTH Peter Whyte

Douglas Dunyan wrote:
>
> If the tablespace only holds the "mytable_" objects, why not
>
> alter tablespace tablespace_name offline;
> drop tablespace tablespace_name including contents;
>
> dm^noise_for_spammer^dunyan_at_hotmail.com
> Douglas Dunyan
> OCP ORACLE V7
> Oracle 8 Master of Technology
> DBA
>
> 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 Wed Oct 28 1998 - 00:00:00 CST