How to drop all user tables in ORACLE?

+1 vote
189 views
asked Jan 30, 2017 by Hitesh Garg (352 points)  

How can I drop all user Tables along with Views, Procedures, Functions, Constraints and everything else in oracle ?

I am facing issue that I am not able to drop because of constraints.

1 Answer

+1 vote
answered Jan 30, 2017 by Rahul Singh (543 points)  
selected Jan 30, 2017 by Hitesh Garg
 
Best answer

One query that I always use to drop everything in my database and make it almost new is -

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
END;

Please try it as I hope it will solve your issue.

124 questions

41 answers

3 comments

928 users

Most popular tags

java python python3 oracle database java-8 sql-server python2 oracle11g git oracle-12g command-line maven sql-server-2012 sql input array list fenitoina dilantin javaçš„ angular javascripts git-branch intellij-idea hashmap collection file-io io temovate clobetasol ampicillin principen daclatasvir daklinza vasotec enalapril nexium esomeprazole tadalafil tadlis sildenafil citrate pefomax xeloda capecitabina clobetasol temovate stemetil prochlorperazine methotrexate methotrexate sildenafilo y dapoxetina super kamagra norvasc amlodipine citalopram celexa amitriptylin saroten vitamin b12 methylcobalamin valaciclovir valtrex zudena udenafil sildenafil citrate vigour depakote divalproex retin-a tretinoine sildenafil citrate ip-max nizoral ketoconazol ciaton tadalafil norethisteronacetat aygestin indomethacin indocin tiova tiotropium bromid finasterida proscar lithium carbonate eskalith finasterida propecia levitra vardenafil vantin cefpodoxima loxitane loxapine rulide roxithromycine avodart dutasterid tecnomax sildenafil citrate methylcobalamin vitamin b12 permethrin acticin etinilestradiol norgestrel ovral pk-merz amantadin cycrin medroxyprogesterone serophene clomiphene spironolactone aldactone mesalazin pentasa zebeta bisoprolol cytodrox hydroxyurea ampicillin ampicillin pletal cilostazol paracetamol panadol pepcid famotidina meclizine hydrochloride antivert donaton tadalafil sdf dsfs sdgfgsdg mac null-pointer-exception constructor oracle-12c terminal hibernate-5 hibernate git-merge es6 programming git-amend git-commit java-stream git-remote multithreading access-modifiers nested-classes
Welcome to Codingeek Q&A, where you can ask questions and receive answers from other members of the community.
...