How to drop all tables from a user

Do help anybody about the query how to drop all tables from a user without droping the user.

Hi akmohanty,

Connect to the user of which the tables have to be dropped.
Using the following select query, the script for dropping all the tables can be prepared.
[B]SQL> select 'drop table ' || tname || ' ;' from tab ;[/B]

Spool the output in a file & the file has to be executed, so that all the tables of the user will be dropped.
U can use "[B]set heading off[/B]" so that the headings display will be eliminated.

Hope it helps u.

create or replace PROCEDURE drop_many_tables
(tablename IN VARCHAR2)
IS
cur integer;
begin
cur:= dbms_sql.OPEN_CURSOR();
for t in (select object_name from user_objects where object_type='TABLE' and object_name like tablename) loop
execute immediate
'drop table ' ||t.object_name|| ' cascade constraints';
end loop;
dbms_sql.close_cursor(cur);
end drop_many_tables;
Try this ...............