How to Drop All Tables in Oracle

How do I drop all tables in an Oracle schema without errors?

Dropping all tables in Oracle removes every user-owned table and its data, freeing the schema for a fresh start.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How can I remove every table in an Oracle schema?

Loop through USER_TABLES (or ALL_TABLES for another user) and run dynamic DROP TABLE statements with CASCADE CONSTRAINTS PURGE. This deletes the tables, their constraints, and their recycle-bin remnants in one go.

What permissions are required?

You need DROP ANY TABLE or ownership of the target tables. To purge another user’s objects, also hold ALTER USER or DBA privileges.

Which PL/SQL block drops all tables safely?

Use DBMS_ASSERT to stop SQL injection, wrap the logic in an anonymous block, and commit afterward to release locks.

Example block

BEGIN FOR t IN (SELECT table_name FROM user_tables) LOOP EXECUTE IMMEDIATE 'DROP TABLE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(t.table_name) || ' CASCADE CONSTRAINTS PURGE'; END LOOP;END;/

How do I target a different schema?

Connect as the schema owner or prefix table names with the schema: EXECUTE IMMEDIATE 'DROP TABLE other_schema.' || t.table_name …

Is there a one-liner alternative?

Yes—drop and recreate the user: DROP USER shop_app CASCADE; then CREATE USER shop_app IDENTIFIED BY ***;. This erases all objects, but also removes grants and quotas.

Best practices for bulk drops

1) Backup data first (expdp). 2) Disable application sessions. 3) Drop in non-production first. 4) Use CASCADE CONSTRAINTS PURGE to avoid leftover metadata. 5) Verify with SELECT COUNT(*) FROM user_tables afterward.

Why How to Drop All Tables in Oracle is important

How to Drop All Tables in Oracle Example Usage


-- Remove every ecommerce table owned by the current schema
BEGIN
  FOR t IN (SELECT table_name FROM user_tables
            WHERE table_name IN ('CUSTOMERS','ORDERS','PRODUCTS','ORDERITEMS')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || t.table_name || ' CASCADE CONSTRAINTS PURGE';
  END LOOP;
END;
/

How to Drop All Tables in Oracle Syntax


BEGIN
  FOR t IN (SELECT table_name FROM user_tables) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(t.table_name) ||
      ' CASCADE CONSTRAINTS PURGE';
  END LOOP;
END;
/

-- Drop all tables for another schema
BEGIN
  FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'SHOP_APP') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE shop_app.' || DBMS_ASSERT.SIMPLE_SQL_NAME(t.table_name) ||
      ' CASCADE CONSTRAINTS PURGE';
  END LOOP;
END;
/

-- Rapid reset by dropping the user
drop user shop_app cascade;
create user shop_app identified by "secret";

-- Ecommerce context
drop table Customers cascade constraints purge;
-- Repeat for Orders, Products, OrderItems if individual removal is preferred.

Common Mistakes

Omitting CASCADE CONSTRAINTS: Oracle raises ORA-02449 when foreign keys exist. Add CASCADE CONSTRAINTS PURGE to remove dependent constraints and bin files in one statement.
Running the script as the wrong user: the loop queries USER_TABLES, so connecting as SYS but targeting APP_USER drops nothing. Connect as the schema owner or change the query to ALL_TABLES with a specific OWNER filter.