Re: Drop ALL Tables!!How??

paul.baumgartel_at_aya.yale.edu wrote in message <6uqsfg$9jj$1_at_nnrp1.dejanews.com>...
>In article <360FA330.8F7CFB6A_at_hongkong.com>,
> Cincout <checkin_at_hongkong.com> wrote:
>> I am new to PL/SQL, I have tried to write a script to drop all tables as
>> follow but enter a runtime error.
>> What wrong is It?
>> Thanks a lot!!
>>
>> Begin
>> for curTable in (select table_name from user_tables) loop
>> DROP TABLE CURTABLE.TABLE_NAME;
>> end loop;
>> END;
>>
>
>You are not allowed to execute static DDL in PL/SQL. Instead, you must use
>dynamic SQL.
>
>Unless you have to do this repeatedly, or have a reason for doing with
PL/SQL,
>it's easier to generate a SQL script using a script such as
>
>set pages 0
>set feedb off
>select 'drop table '||table_name||' cascade constraints;' from user_tables
>
>spool dropt.sql
>/

It will not work if you have constraints. Try this:

drop user cascade;

from SQL. It drops all tables and remove all constraints (but also remove all other objects owned by user). Received on Tue Sep 29 1998 - 22:35:13 CDT