7
Contributors
6
Replies
7
Views
10 Years
Discussion Span
Last Post by vijin
0

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.

0

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

0

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.

0
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>

0
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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.