Re: How to drop all views or tables?

[Many people wrote similar articles]
>First, connect as the user whose tables you wish to drop. Then type in the
>following statement:
>SELECT 'DROP TABLE '||tname||';' FROM TAB
>Save this command in your buffer by pressing return twice to get back to the
>SQL> prompt. Then

The major problem with this is that versions of Oracle later than version 6.0.3x (30 or 33) introduced a concept called "Constraints". Tables which have constraints upon them from other objects can not be dropped. You will receive an error that some foreign key constraints refer to the table.

  create table area_code (

	code char(3) not null,
	state char(2) not null references state

Unless your tables just HAPPEN to have constraints applied in alphabetical order from most dependent to least dependent constraint, it will fail. One solution is to run the created drop script many times until no "constraint" errors occur. Another solution is to write a drop script which also generates the MODIFY TABLE DROP CONSTRAINT constraint_name statements prior to dropping the table. This is a bit cleaner than using a SQL*Plus facility to do CONNECT BY/START WITH to drop tables in the "proper" order.

-Andy

-- 
Andrew Finkenstadt, Vista-Chrome, Inc., Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager, andy_at_vistachrome.com, andy_at_genie.geis.com.
  Send mail to ora-request_at_vistachrome.com to join Unix, CASE, and 
  Desktop Oracle RDBMS Database discussions.