How to drop all user tables?

How can I drop all user tables in oracle?

I have problem with constraints. When I disable all it is still no possible.

9 Answers

BEGIN FOR cur_rec IN (SELECT object_name, object_type FROM user_objects WHERE object_type IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE', 'SYNONYM', 'PACKAGE BODY' )) LOOP BEGIN IF cur_rec.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"' ); END; END LOOP; FOR cur_rec IN (SELECT * FROM all_synonyms WHERE table_owner IN (SELECT USER FROM dual)) LOOP BEGIN EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || cur_rec.synonym_name; END; END LOOP; END; / 

If you just want a really simple way to do this.. Heres a script I have used in the past

select 'drop table '||table_name||' cascade constraints;' from user_tables; 

This will print out a series of drop commands for all tables in the schema. Spool the result of this query and execute it.

Source:

Likewise if you want to clear more than tables you can edit the following to suit your needs

select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX') 

Another answer that worked for me is (credit to )

BEGIN FOR c IN (SELECT table_name FROM user_tables) LOOP EXECUTE IMMEDIATE ('DROP TABLE "' || c.table_name || '" CASCADE CONSTRAINTS'); END LOOP; FOR s IN (SELECT sequence_name FROM user_sequences) LOOP EXECUTE IMMEDIATE ('DROP SEQUENCE ' || s.sequence_name); END LOOP; END; 

Note that this works immediately after you run it. It does NOT produce a script that you need to paste somewhere (like other answers here). It runs directly on the DB.

begin for i in (select 'drop table '||table_name||' cascade constraints' tbl from user_tables) loop execute immediate i.tbl; end loop; end; 

The simplest way is to drop the user that owns the objects with the cascade command.

DROP USER username CASCADE 

SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables; 

user_tables is a system table which contains all the tables of the user the SELECT clause will generate a DROP statement for every table you can run the script

The easiest way would be to drop the tablespace then build the tablespace back up. But I'd rather not have to do that. This is similar to Henry's except that I just do a copy/paste on the resultset in my gui.

SELECT 'DROP' ,object_type ,object_name ,CASE(object_type) WHEN 'TABLE' THEN 'CASCADE CONSTRAINTS;' ELSE ';' END FROM user_objects WHERE object_type IN ('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION','SEQUENCE') 

To remove all objects in oracle :

1) Dynamic

DECLARE CURSOR IX IS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE ='TABLE' AND OWNER='SCHEMA_NAME'; CURSOR IY IS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION', 'VIEW') AND OWNER='SCHEMA_NAME'; CURSOR IZ IS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TYPE') AND OWNER='SCHEMA_NAME'; BEGIN FOR X IN IX LOOP EXECUTE IMMEDIATE('DROP '||X.OBJECT_TYPE||' SCHEMA_NAME.'||X.OBJECT_NAME|| ' CASCADE CONSTRAINT'); END LOOP; FOR Y IN IY LOOP EXECUTE IMMEDIATE('DROP '||Y.OBJECT_TYPE||' SCHEMA_NAME.'||Y.OBJECT_NAME); END LOOP; FOR Z IN IZ LOOP EXECUTE IMMEDIATE('DROP '||Z.OBJECT_TYPE||' SCHEMA_NAME.'||Z.OBJECT_NAME||' FORCE '); END LOOP; END; / 

2)Static

 SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables union ALL select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION') union ALL SELECT 'drop ' ||object_type ||' ' || object_name || ' force;' FROM user_objects WHERE object_type IN ('TYPE'); 

Please follow the below steps.

begin for i in (select 'drop table '||table_name||' cascade constraints' tb from user_tables) loop execute immediate i.tb; end loop; commit; end; purge RECYCLEBIN;