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

If you need to drop all tables in the database with Oracle, here’s an easy way!

 

run this command:

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

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

 


Posted

in

by

Comments

12 responses to “An Easy Way to Drop All Tables in Your Tablespace in Oracle”

  1. Vincent Avatar
    Vincent

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

  2. Joe Avatar
    Joe

    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>

    1. Devon Avatar

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

  3. S.MOHANKUMAR Avatar
    S.MOHANKUMAR

    Super Idea. Really Helps, After Running this we can Drop Table Space

  4. Shreya Rugle Avatar
    Shreya Rugle

    Thank you!

  5. m Avatar
    m

    thnx alot dude

  6. iheb Avatar
    iheb

    Thank you

  7. Juliana Avatar
    Juliana

    Sensacional!! Muito obrigada!!!

  8. ahmed salah Avatar
    ahmed salah

    How much clever Idea. So thanks.

  9. Ayyappa Avatar
    Ayyappa

    Thank u

  10. ayoub dkhissi Avatar
    ayoub dkhissi

    that’s clever! thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

PHP Code Snippets Powered By : XYZScripts.com