Ad:

Oracle Discussion Thread
Unsolved
Views: 6451

Permalink

Mar 31st, 2007

deleting the tables

Expand Post »

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

Similar Threads

Searching for a record in multiple tables in VB.NET
Deleting a Row From A DataGrid in VB.NET
Delete from multiple tables in MySQL
Code not working for deleting recordfrom a file in Visual Basic 4 / 5 / 6
IE Cache not deleting a URL in Web Browsers

satish.paluvai

Reputation Points: 9

Solved Threads: 1

Light Poster

Offline

45 posts
since Mar 2007

Permalink

Mar 31st, 2007

Re: deleting the tables

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.

davidcairns

Reputation Points: 12

Solved Threads: 8

Junior Poster

Offline

114 posts
since Feb 2007

Permalink

Apr 12th, 2007

Re: deleting the tables

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

wavalker

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

11 posts
since Apr 2007

Permalink

Jul 14th, 2007

Re: deleting the tables

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

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Aug 23rd, 2007

Re: deleting the tables

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.

Musta

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Aug 2007

Permalink

Mar 13th, 2009

Re: deleting the tables

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.

bala4901

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jul 2008

Permalink

Jan 6th, 2011

Re: deleting the tables

Oracle Syntax (Toggle Plain Text)
DECLARE
	table_name VARCHAR2(30);
	   CURSOR usertables IS SELECT * FROM user_tables WHERE table_name NOT LIKE 'BIN$%';
	BEGIN
	   FOR next_row IN usertables
	   LOOP
	      EXECUTE IMMEDIATE 'drop table ' || next_row.table_name || ' cascade constraints';
	   END LOOP;
     END;
DECLARE
	table_name VARCHAR2(30);
	   CURSOR usertables IS SELECT * FROM user_tables WHERE table_name NOT LIKE 'BIN$%';
	BEGIN
	   FOR next_row IN usertables
	   LOOP
	      EXECUTE IMMEDIATE 'drop table ' || next_row.table_name || ' cascade constraints';
	   END LOOP;
     END;

save it as a .sql file and run it from oracle sql plus using this command @path\filename.SQL / will remove all tables from USER_TABLES (where all the user tables are created ) in the current schema pointed to SQL PLUS

Regards
VIJINDAS

vijin

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Aug 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Oracle Forum Timeline: Inserting a new column!!!!!!!!

Next Thread in Oracle Forum Timeline: oracle 11i

DaniWeb Message

Cancel Changes

User Name
Password