-
Junior Member
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.
-
Expert Member
Re: How to drop all tables from a 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.
SQL> select 'drop table ' || tname || ' ;' from tab ;
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 "set heading off" so that the headings display will be eliminated.
Hope it helps u.
Last edited by Innila; 03-05-2007 at 01:41 AM.
*** Innila ***
-
Expert Member
Re: How to drop all tables from a user
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 ...............
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules