• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Drop tables recursively

 
Jane Somerfield
Ranch Hand
Posts: 193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to drop a group of tables, whose names are started with �MY_�. Because the number of the table is very large (around 200), I want to do it recursively. Here is the SQL with subquery, but it is not working.

DROP TABLE (select TABLE_NAME from ALL_TABLES where TABLE_NAME like 'MY_%') CASCADE CONSTRAINTS PURGE;

Any suggestions? Any Oracle (PL/SQL) functions can do it?
 
Edisandro Bessa
Ranch Hand
Posts: 584
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jane,

I think you could try something like this :

SELECT 'DROP TABLE ' + TABLE_NAME + ' CASCADE CONSTRAINTS PURGE; '
FROM ALL_TABLES WHERE TABLE_NAME LIKE 'MY_%'

The above SQL statement would create 200 drop commands like this :

DROP TABLE MY_TABLE1 CASCADE CONSTRAINTS PURGE;
DROP TABLE MY_TABLE2 CASCADE CONSTRAINTS PURGE;
DROP TABLE MY_TABLE3 CASCADE CONSTRAINTS PURGE;
DROP TABLE MY_TABLE4 CASCADE CONSTRAINTS PURGE;

... And so on ...

I'm not sure about oracle PL-SQL syntax, so you can adapt the sql statement above according to your needs.

If the first command works on Oracle, all of them will work.
Everything you have to do is submit all these commands in only one shot.
[ May 24, 2007: Message edited by: Edisandro Bessa ]
 
Jane Somerfield
Ranch Hand
Posts: 193
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried. But got an Error "ORA-01722: invalid number"
 
Scott Selikoff
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I doubt there is a way to do it mostly because creating/dropping is not something that should be done frequently (such that optimization is really necessary).

If you're in a situation where tables need to be dropped often (especially 200), I'd wonder if you should rethink your schema. BTW, even if you can't do this in PLSQL, you can always do what your describing pretty easily in JDBC just using a while loop on the first SELECT statement, but again, I'd wonder more about why this is necessary.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic