How to drop all tables that I have created by sql statement?

Question

drop all tables;

or drop all tables from database;

Answer 1

One way to do this would be to generate all the DDL statements using the metadata table

select 'DROP TABLE '||table_name||';'
  from user_tables

Run the query and copy-paste the output which would contains the statements for dropping tables

Answer 2

One solution would be to DROP the user that owns the objects with the CASCADE option.

DROP USER myuser CASCADE;

This will efficiently drop all user objects (tables, views, packages, procedures, ...). You will then need to recreate the user.

---

Else, if you want to limit the scope of your action to just dropping all of your tables (and associated constraints), you could write a procedure that dynamically generates and executes a series of DROP TABLE statements :

BEGIN
   FOR cur_rec IN (SELECT object_name FROM user_objects WHERE object_type = 'TABLE')
   LOOP
      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line ('FAILED: DROP TABLE "' || cur_rec.object_name || '"');
      END;
   END LOOP;
END;
/