Thursday, July 24, 2008

Drop All Tables in a Single Query

Recently, I needed to find a single, simple PL/SQL command to drop all the tables from an Oracle database without explicitly naming each table and without dropping the entire schema. Application development has just started, and I need to easily rename tables and other database objects, so the simplest solution at this early stage is often just to wipe and recreate the entire schema.
One possible solution would be to login as SYS and drop the schema itself, but I need to configure a single login for any database access, i.e., the user whose database objects will be deleted.

I did not find my specific solution online, but I owe thanks to alternative approaches in some other posts.

First, make sure that you connect to Oracle as the correct user, i.e., the one whose tables will be dropped.

This command will drop all the user's tables:
BEGIN 
  FOR i IN (SELECT table_name FROM user_tables) 
    LOOP 
      EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i.table_name || ' CASCADE CONSTRAINTS'); 
    END LOOP
END;

Similar commands can be used to drop all triggers, sequences, etc.  For example,
BEGIN 
  FOR i IN (SELECT trigger_name FROM user_triggers) 
    LOOP 
      EXECUTE IMMEDIATE('DROP TRIGGER ' || user || '.' || i.trigger_name); 
    END LOOP
END;

               and

BEGIN 
  FOR i IN (SELECT sequence_name FROM user_sequences) 
    LOOP 
      EXECUTE IMMEDIATE('DROP SEQUENCE ' || user || '.' || i.sequence_name); 
    END LOOP
END;

NB: if you drop all tables before triggers, your triggers will be renamed with special characters and the command might not work; make sure to drop triggers before tables.

29 comments:

  1. Where was this post when I needed it a year ago? :)

    ReplyDelete
  2. Thanks for the comment, and I totally hear you. It seems like such an obvious thing to know, and it should come up right away in any web search, but it took me more than a minute to find the answer, so I figured it was worth posting. Hopefully this will save someone some time, even if it is too late to help you....
    -----Tim-----

    ReplyDelete
  3. I should mention that this is what I did...

    declare
    cursor dropObjectsCusor is
    select 'drop '|| object_type || ' ' || object_name as sqlDropStmt
    from user_objects
    where object_type <> 'TABLE' and object_type <> 'INDEX'
    order by object_type;

    cursor dropTablesCusor is
    select 'truncate table ' || object_name as sqlTruncTbl,
    'drop table ' || object_name || ' cascade constraints' as sqlDropTbl
    from user_objects
    where object_type = 'TABLE'
    order by object_type;


    begin

    for ob in dropObjectsCusor
    loop
    execute immediate ob.sqlDropStmt;
    end loop;

    for ob in dropTablesCusor
    loop
    execute immediate ob.sqlTruncTbl;
    execute immediate ob.sqlDropTbl;
    end loop;

    ReplyDelete
  4. Great contribution. I may end up using some form of your script in the future. Thanks!
    -----Tim------

    ReplyDelete
  5. Awesome script! Just what I needed! Thanks, you saved me a great deal of time...

    ReplyDelete
  6. Hi Emperor361,

    Thanks for the feedback! Let me know if you come up with any improvements.

    ---Tim---

    ReplyDelete
  7. Thank you, it works great. I was lucky to find this post
    Svetlana

    ReplyDelete
  8. Hi Svetlana,

    Thanks for the feedback. If you run into any issues or have ideas for improvements, let me know!

    ---Tim---

    ReplyDelete
  9. Thank you ! It worked great ! I will visit this blog often.

    ReplyDelete
  10. Hi Ranganathan,

    I appreciate the feedback and am happy that it worked for you!

    ---Tim---

    ReplyDelete
  11. Thanks.. It saved a lot of my time.

    ReplyDelete
  12. You could try this logging in as sysdba (just one log in will do the job)

    For all tables
    ==============

    SELECT 'DROP TABLE ' || owner || '.' || table_name || ' CASCADE CONSTRAINTS;'
    FROM all_tables
    /


    For all triggers
    ================
    SELECT 'DROP TRIGGER ' || owner || '.' || trigger_name || ' ;'
    FROM all_triggers
    /



    For all sequences
    =================
    SELECT 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ' ;'
    FROM all_sequences
    /

    Custom for specific owners/users (but make sure you log in as SYSDBA) Also substitute the sql above if tables, triggers or sequences and include/exclude users/owners as needed
    ====================================================================


    Example for sequence

    SELECT 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ';'
    FROM all_sequences
    where sequence_owner IN (
    'SCOTT',
    'SH',
    'SSS',
    'TEST1',
    .
    .
    .
    .
    .
    'WHD'
    )

    order by sequence_owner

    ReplyDelete
  13. Thanks for the feedback, Suzanne! Glad you found the post useful.

    ReplyDelete
  14. ya solution is quite brilliant that we can delete all the tables from the database at a time...... but how we can delete only 3tables (dept,emp,salgrade) from the database when there are 15 different tables from the same database.

    thanks in advance

    regards,
    thanmathpradeep@gmail.com

    ReplyDelete
  15. Hi Pradeep,

    If I understand your question correctly, I think I would modify the inner query for finding all the schema tables to "SELECT table_name FROM user_tables WHERE table_name IN ('dept','emp','salgrade')".

    Let me know if that is what you were looking for.

    Thanks,
    ---Tim---

    ReplyDelete
  16. hello Tim Myer::

    my question is that ........... in my database i have 5tables i.e.,(emp,dept,salgrade,bonus,sales).

    In that 5tables am looking to delete only three table i.e,(emp,dept,bonus) at a time and the remaining tables should be in the database itself.

    This is my small requirement.thanks in advance

    regards,
    thanmathpradeep@gmail.com

    ReplyDelete
  17. Hi Pradeep,

    It appears that I did understand your question correctly. Did you attempt what I suggested?

    ---Tim---

    ReplyDelete
  18. HELLO Tim Myer ,

    I HAD TRIED UR COMMAND IT WAS EXCELLENT AND U HAD CREATED THE ABOVE MENTIONED COMMAND TO DROP ALL THE TABLES FROM THE DATABASE AT A TIME.

    BUT AM ASKING IN A DIFFERENT WAY,LET ME EXPLAIN WITH ONE EXAMPLE ,,

    LET AS ASSUME THE FOLLOWING THINGS...

    (1) ASSUME THAT THE NAME OF THE DATABASE IS "PRADEEP"

    (2) ASSUME THAT THERE ARE 8 TABLES IN THIS DATABASE.

    (3) HERE I WOULD LIKE TO DROP ONLY FOUR TABLES AT A TIME AND I WOULD LIKE TO KEEP THE REMAINING TABLES.

    SELECT *FROM TAB;

    TABLE_NAME TABLE_TYPE

    DEPT TABLE
    EMP TABLE
    SALGRADE TABLE
    BONUS TABLE
    INTEREST TABLE
    STUDENT_INFO TABLE
    BANK_INFO TABLE
    ECONOMY TABLE

    HERE FROM THE ABOVE TABLES , I WOULD LIKE TO DROP FOUR TABLES I.E.,DEPT,EMP,SALGRADE,BONUS.

    WE CAN DROP THOSE TABLES INDIVIDUALLY AS,

    SQL>DROP TABLE DEPT;
    TABLE DROPPED.

    SQL>DROP TABLE EMP;
    TABLE DROPPED.

    SQL>DROP TABLE SALGRADE;
    TABLE DROPPED.

    SQL>DROP TABLE BONUS;
    TABLE DROPPED.

    BUT I WON'T LIKE TO DO THESE OPERATIONS SEPARATELY. I NEEDED A SINGLE,SIMPLE PL/SQL COMMAND TO DROP THESE FOUR TABLES FROM AN ORACLE DATABASE WITH NAMING EACH TABLE AND WITHOUT DROPPING THE ENTIRE SCHEMA.

    HOPE YOU UNDERSTAND MY WORDS AND EXPECTATIONS .........
    THANKS AGAIN


    REGARDS,
    THANMATHPRADEEP@GMAIL.COM

    ReplyDelete
  19. Hi Pradeep,

    Yes, I understand what you are asking. I will ask you again, did you try what I suggested above?

    BEGIN
    FOR i IN (SELECT table_name FROM user_tables
    WHERE table_name IN ('DEPT','EMP','SALGRADE','BONUS'))
    LOOP
    EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i.table_name || ' CASCADE CONSTRAINTS');
    END LOOP;
    END;

    You may need to uppercase the table name.

    ---Tim---

    ReplyDelete
  20. thank you sir, my doubt was clarified.

    regards,
    thanmathpradeep@gmail.com

    ReplyDelete
  21. hello sir,
    I have another requirement ...........

    let us consider the following table.

    select *from pradeep;

    ename sal deptno
    ----- ----- --------
    pradeep 5000 10
    prasad 2000 20
    tulasi 3530 30
    venkat 4400 40
    sachin 1000 50
    dravid 5000 60
    sewag 10000 70

    ( sir,this is the related table)

    my requirement is that

    i want the get the out put as mentioned below,

    ename sal deptno
    ----- ----- --------

    prasad 2000 20
    tulasi 3530 30
    venkat 4400 40

    my condition is that, the above mentioned data should be retrieved from the base table only when deptno is present in the sal

    i.e., sal=(20)00 where deptno=20;
    sal=35(30) where deptno=30;
    sal=4(40)0 where deptno=40;

    hope you understood my requirement..

    thanks in advance....


    regards,
    thanmathpradeep@gmail.com

    ReplyDelete
  22. YA I GOT IS SUCCESSFULLY ,

    I SOLVED IT BY D FOLLOWING QUERY

    SELECT A.ENAME,B.SAL,A.COMM.B.DEPTNO FROM PRADEEP A,PRADEEP B WHERE A.DEPTNO=B.DEPTNO AND INSTR(A.SAL,B.DEPTNO)>0;

    REGARDS,
    THANMATHPRADEEP@GMAIL.COM

    ReplyDelete
  23. hi pradeep,

    I have a backup 10g database server I have daily full export dmp file of my active database server. can you guide me how I can update my backup database server from full export file on daily basis. I want to import file on daily basis on my backup server to upto date my backup database server.

    Thanks in advance

    ReplyDelete
  24. Muhammad Uzair (ORACLE APEX Developer)

    Thanks a lot for sharing this nice information to save the time.

    ReplyDelete
  25. Thanks for sharing this helpful information to save my time.

    ReplyDelete
  26. A really helpful bit of code for someone new to Oracle.

    Thanks

    ReplyDelete
  27. -- Why is this procedure not giving the output I intended-- delete all views
    --It compiles fine and tests fine on pl/sql developer, but never deletes the views
    -- I used your code and it worked fine

    CREATE OR REPLACE PROCEDURE delete_views IS

    v_qtext VARCHAR2(100);
    rec_views VARCHAR2(40);
    old_views VARCHAR2(40);

    CURSOR rec_views IS(
    SELECT ut.view_name view_name
    FROM user_views ut
    ORDER BY view_name);
    BEGIN

    OPEN rec_views;
    FETCH rec_views
    INTO old_views;
    IF rec_views%NOTFOUND
    THEN
    v_qtext := 'DROP VIEW ' || old_views;
    EXECUTE IMMEDIATE v_qtext;
    END IF;
    CLOSE old_views;
    END delete_views;

    ReplyDelete