How to drop all user tables?

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: https://forums.oracle.com/forums/thread.jspa?threadID=614090

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 http://snipt.net/Fotinakis/drop-all-tables-and-constraints-within-an-oracle-schema/)

BEGINFOR c IN (SELECT table_name FROM user_tables) LOOPEXECUTE IMMEDIATE ('DROP TABLE "' || c.table_name || '" CASCADE CONSTRAINTS');END LOOP;FOR s IN (SELECT sequence_name FROM user_sequences) LOOPEXECUTE 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.