deleting the tables

[satish.paluvai]

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

[davidcairns]

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.

[wavalker]

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

[debasisdas]

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

[Musta]

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.

[bala4901]

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,
Fellow Developer
---------------------------------------------------------------------
Visit URL:

[vijin]

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