Re: dropping a large number of tables?

In article <70j8if$qvp$1_at_nnrp1.dejanews.com>,   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
>

Greetings, Dave,

I support a very large application with 1900+ tables; these tables are all owned by the same user. In order to drop all of these tables, I created the following script:

  set heading off
  spool droptbl.lst
  spool drop.sql

  select 'drop table ' || table_name || ' cascade constraints; '   from dba_tables
  where owner = 'SYSADM'; (you would put the owner of the tables here)

  spool off
  set heading on

I could just run 'drop.sql' by putting a @drop after the 'spool off', but I'm paranoid, and like to check things out.

This script generates the DROP TABLE for each of the 1900+ tables. Your script would look something like this -

  set heading off
  spool a.lst
  spool b.sql

  select 'drop table ' || table_name || ' cascade constraints; '   from dba_tables
  where table_name like 'mytable_%';

  spool off

You would then run b.sql in SQL*Plus as @b (or whatever name you gave it).

Hope this helps - take care.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Oct 22 1998 - 00:00:00 CDT