Re: Drop tables in Oracle

Um, maybe I'm oversimplifying, but you could write and save a script similar to the following and then run it from SQL*Plus when desired:

(none of this tested, the Oracle instance on my machine keeps dying when an NT explorer.exe crash takes everything with it, so you may need to debug these)

run as the owner of the tables:
SELECT 'drop table '||table_name||';'
FROM user_tables
WHERE table_name like 'A_%'
(or)
WHERE table_name like '&bgn_chars%';

run as user with dba privs:
SELECT 'drop table '||owner||'.'||table_name||';' FROM dba_tables
WHERE table_name like 'A_%'
(or)
WHERE table_name like '&bgn_chars%';

You may need to play with adding the cascade option in the drop command.

I like to use SQL-Station. Like many Oracle DB browsers it has a filter on the objects you see. You could tell it to only show you objects beginning with certain characters. Then you shift-click to highlite as many tables as you want to drop, then right-click and Delete. If it runs into tables with dependencies, it asks if you want to drop cascade, I keep answering Yes until they're all gone. Voila! Nice. Perhaps your 3rd part tools or OEM has the same ability. In PL/SQL you'd write similar code, but using the DBMS_SQL package, or if you have 8i, the EXECUTE IMMEDIATE feature (which is sweet, I'm in love with our 8i instances). Hope that helps.
-bill
(remove the nullspm to reply)

Dana Jian wrote:

> Hi,
>
> Is there any way to Drop tables which starts with like A_... in PL/SQL or in
> any tools of Oracle?
>
> Thanks,
> Dana
> djian_at_trasa.com
Received on Mon Jan 24 2000 - 10:26:43 CST