How to drop all user tables in ORACLE?

0 votes
114 views
asked Jan 30 by Hitesh Garg (167 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 by Rahul Singh (253 points)  
selected Jan 30 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.

36 questions

24 answers

3 comments

18 users

Welcome to Codingeek Q&A, where you can ask questions and receive answers from other members of the community.
...