• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12328
  • Last Modified:

Drop all tables for a particular user

I need to drop all tables for a particular user.  What's the command?
0
lcor
Asked:
lcor
5 Solutions
 
plamen73Commented:
There is no single command but you can do it with the following script:

-- Uncomment the last line when you are sure what you are doing!!!! ---
-- also change PLAMEN with the user you want to leave without tables
---------------------------------------------
set pagesize 0;
set feedback off;

spool D:\temp\drop.sql;
select 'drop table ' || t.owner || '.' || t.table_name || ' cascade constraint;'
from all_Tables t
where t.owner = 'PLAMEN';
spool off;

--@@D:\temp\drop.sql;


Of course you must log in as user that has the privilege to drop the PLAMEN's tables
0
 
slightwv (䄆 Netminder) Commented:
The only single command for this is to drop the user.

You will need to issue each drop statement individually of use a PL/SQL block and a loop.

--  the cascade gets rid of possible foreign key errors
drop table <table_name> cascade;
0
 
awking00Commented:
create or replace procedure drop_user_tbls is
sql_stmnt    VARCHAR2(255);
begin
for uname in
(select owner, table_name
 from dba_tables
 where owner = <userid>)
loop
sql_stmnt := 'drop table '||uname.owner||'.'||uname.table_name;
execute immediate sql_stmnt;
end loop;
end;
/
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
awking00Commented:
slightwv has a good point. Modify above code as
sql_stmnt := 'drop table '||uname.owner||'.'||uname.table_name||' cascade';
0
 
MikeOM_DBACommented:

Why not just:

DROP USER <userx> CASCADE;

And the create it again!




0
 
lcorAuthor Commented:
I keep getting missing keyword when I add cascade.  Even when I do a single sql statement in sqplus.  What's missing?
0
 
lcorAuthor Commented:
Forgot ...
cascade constraint

Any opinions on MikeOM_DBA  solution to drop user?
0
 
lcorAuthor Commented:
When I use awking00 's solution, I get the following compilation errors:

Errors for PROCEDURE DROP_USER_TBLS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: SQL Statement ignored
6/7      PL/SQL: ORA-00942: table or view does not exist
9/1      PL/SQL: Statement ignored
9/29     PLS-00364: loop index variable 'UNAME' use is invalid
0
 
lcorAuthor Commented:
figured it out...gave select any dictionary, table privelage to user
0
 
schwertnerCommented:
Change DBA_TABLES with USER_TABLES and run it from the particular user acount.

DROP TABLE will make entries in the REDO LOG files. If you would like to do the job fast
use TRUNCATE TABLE.
Warning: You will not be able to rollback.
0
 
awking00Commented:
<<Errors for PROCEDURE DROP_USER_TBLS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: SQL Statement ignored
6/7      PL/SQL: ORA-00942: table or view does not exist >>

You apparently do not have select rights to dba_tables. You might try all_tables instead or, if you're able to logon as the user whose tables are to be dropped, you can definitely use user_tables. In the latter case the code becomes even simpler:
for tname in
(select table_name
 from user_tables)
loop
sql_stmnt := 'drop table '||tname.table_name;
execute immediate sql_stmnt;
end loop;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now