Oracle

deleting the tables

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Mar 2007

Posts: 45

Reputation: satish.paluvai is an unknown quantity at this point

Solved Threads: 1

satish.paluvai satish.paluvai is offline

Offline

Light Poster

deleting the tables

Mar 31st, 2007

how to delete all the tables in the a user at a time?

•

Join Date: Feb 2007

Posts: 114

Reputation: davidcairns is an unknown quantity at this point

Solved Threads: 8

davidcairns davidcairns is offline

Offline

Junior Poster

Re: deleting the tables

Mar 31st, 2007

There is no one command to do this, you could write a PL procedure to do it, or generate the SQL statements from USER_TABLES. Alternately some IDE's provide an easier way.

•

Join Date: Apr 2007

Posts: 11

Reputation: wavalker is an unknown quantity at this point

Solved Threads: 1

wavalker

Offline

Newbie Poster

Re: deleting the tables

Apr 12th, 2007

if in yor databse you have only tables then simply can drop user
or u can create one one store procedure to delete all table from your databse

•

Join Date: Feb 2007

Posts: 2,086

Reputation: debasisdas will become famous soon enough

Solved Threads: 125

debasisdas debasisdas is offline

Offline

Postaholic

Re: deleting the tables

Jul 14th, 2007

try to write a script for this
or
a better option would be to write a cursor and then drop the tables .

Share your Knowledge.

•

Join Date: Aug 2007

Posts: 6

Reputation: Musta is an unknown quantity at this point

Solved Threads: 0

Musta

Offline

Newbie Poster

Re: deleting the tables

Aug 23rd, 2007

You have two options:
---------------------------------------
1) Create a script
set lines 200;
set pages 10000;
spool c:\DROP_TABLES.txt;
select 'DROP TABLE '||table_name||';'
from all_tables
where owner = 'SCOTT';
-- change the SCOTT to the owner of the tables you need to drop.
spool off;
---------------------------------------
@c:\DROP_TABLES.txt; -- to run the script that we created.
-- repeat this step tell u drop all the tables.
---------------------------------------

2) From the Enterprise Manager , Drop the owner Schema.

Regards.

•

Join Date: Jul 2008

Posts: 1

Reputation: bala4901 is an unknown quantity at this point

Solved Threads: 0

bala4901

Offline

Newbie Poster

Re: deleting the tables

Mar 13th, 2009

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
BEGIN
FOR c IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE ('drop table '||c.table_name||' cascade constraints);
end loop;
End;
 
Begin
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints);
end loop;
End;

Best regard,
<URL SNIPPED>Fellow Developer
---------------------------------------------------------------------
Visit URL: <URL SNIPPED>

Last edited by peter_budo; Mar 18th, 2009 at 7:33 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.