?
Solved

Drop all tables for a particular user

Posted on 2005-04-14
11
Medium Priority
?
12,317 Views
Last Modified: 2012-06-27
I need to drop all tables for a particular user.  What's the command?
0
Comment
Question by:lcor
11 Comments
 
LVL 4

Assisted Solution

by:plamen73
plamen73 earned 600 total points
ID: 13782542
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 13782559
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
 
LVL 32

Accepted Solution

by:
awking00 earned 800 total points
ID: 13783018
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 32

Expert Comment

by:awking00
ID: 13783047
slightwv has a good point. Modify above code as
sql_stmnt := 'drop table '||uname.owner||'.'||uname.table_name||' cascade';
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 200 total points
ID: 13785410

Why not just:

DROP USER <userx> CASCADE;

And the create it again!




0
 

Author Comment

by:lcor
ID: 13785437
I keep getting missing keyword when I add cascade.  Even when I do a single sql statement in sqplus.  What's missing?
0
 

Author Comment

by:lcor
ID: 13785567
Forgot ...
cascade constraint

Any opinions on MikeOM_DBA  solution to drop user?
0
 

Author Comment

by:lcor
ID: 13786285
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
 

Author Comment

by:lcor
ID: 13786554
figured it out...gave select any dictionary, table privelage to user
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 200 total points
ID: 13788557
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
 
LVL 32

Expert Comment

by:awking00
ID: 13791229
<<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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question