Re: Remove all tables for user with one SQl statement?

Jan Doggen wrote:
> Hi
>
> How can I remove all tables
> UserName.TblNam1
> UserName.TblNam2
> UserName.TblNam3
> etc
>
> with one SQL statement?
>
> Dropping the user is not allowed.
>
> Thanks in advance
> Jan

You can't.
You can of course
begin
for tab in (select table_name from dba_tables where owner= 'UserName' ) loop
execute immediate 'drop table Username.'||tab.table_name||' cascade constraints';
end loop;
end;
/
But you would need to make sure there are no foreign keys or drop the tables in the right order.
Maybe you are disallowed to drop the user for a reason, and you would better not allowed to drop tables too.

-- 
Sybrand Bakker
Senior Oracle DBA