An Easy Way to Drop All of the Tables in Your Tablespace in Oracle

Home/Database/An Easy Way to Drop All of the Tables in Your Tablespace in Oracle

run this command:

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

Then copy the output and run that as a sql script.

By | 2017-05-18T15:18:12+00:00 March 8th, 2008|Database|4 Comments

About the Author:

4 Comments

  1. dsfa December 1, 2016 at 8:38 pm - Reply

    Nice

  2. Vincent May 28, 2017 at 9:16 pm - Reply

    This would drop all tables that belongs to user, not just one tablespace

  3. Joe August 16, 2017 at 8:36 am - Reply

    When I run this, I get the following, but it didn’t drop the tables.

    ‘DROPTABLE’ TABLE_NAME ‘CASCADECONSTRAINTS;
    ———– —————————— ——————–
    drop table WM_AGENT_TIMEOFFS cascade constraints;
    drop table WM_MARKED_TIMES cascade constraints;
    drop table WM_AGENT_STATES cascade constraints;
    drop table WM_FIXED_STATES cascade constraints;
    drop table WM_SUBSYSTEMS cascade constraints;
    drop table WM_FORECAST_DATA cascade constraints;
    drop table WM_SUBSYSTEM_GROUPS cascade constraints;
    drop table WM_SCH_AUDIT_LOG cascade constraints;
    drop table WM_AGENT_EVENTS cascade constraints;
    drop table WM_CFG_AUDIT_INFO cascade constraints;
    drop table WM_REPORT_TASKS cascade constraints;

    132 rows selected.

    SQL>

    • Devon August 16, 2017 at 8:39 am - Reply

      That just generates the SQL. You then copy/paste all the middle lines “drop table… constraints;” and run those as a sql script.

Leave A Comment