You may have trouble with foreign key constraints.

Below drops everything. Change it to reference user_tables OR change
the where clause to only include TABLES. If you don't take care of the
FK constraints then you will be re-running a script over and over until
all the tables are done.

select 'drop '||object_type||' "'||object_name||'"'||
decode(object_type,'TABLE',' cascade constraints;',';')
from user_objects
where object_type not in ('INDEX','TRIGGER','PACKAGE BODY','LOB')
order by object_id;

Joel Patterson
Database Administrator
joel.[Email address protected]
x72546
904 727-2546

-----Original Message-----
From: Bashar Shannak
[mailto:oracledba-ezmlmshield-x38051137.[Email address protected]
Sent: Tuesday, October 31, 2006 1:44 AM
To: LazyDBA Discussion
Subject: Re: Dropping the tables

Hi
just run this script on the user schema that you want to drop his/her
tables:

SELECT ' DROP TABLE ' || tname || ' ;' from tab ;

this will generate drop statement for all his/her tables so either you
save the result in file or copy the result to the editor

Thanks
Bashar Shannak
Maktoob Inc
>
>
>
>
> ----- Original Message -----
> From: Raj "oracledba-ezmlmshield-x93527208.[Email address protected]
> To: LazyDBA Discussion "[Email address protected]
> Date: 06:57 AM
> Subject: Dropping the tables
>
>
>
> Hello All,
>
>
>
> Kindly let me know how can we drop all the tables owned by a user
without
> dropping the user.
>
>
>
>
>
> Kind regards,
>
> Raj
>
>
>
>
>
> ---------------------------------------------------------------------
>
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>
> To post a dba job: http://jobs.lazydba.com
>
> To Subscribe : http://www.LazyDBA.com
>
> To unsubscribe: http://www.lazydba.com/unsubscribe.html


Bashar Shannak
Database Administrator
Maktoob Inc.


_________________________________________________
Create your album and Share the best photos EVER with Maktoob Photo
Album!
http://www.maktoob.com/photos


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page