How to drop all user tables

Question

How can I drop all user tables in oracle   I have problem with constraints  When I disable all it is still no possible

User · Answer

begin   for i in  select  drop table    table name    cascade constraints  tbl from user tables     loop      execute immediate i tbl    end loop  end

User · Answer

The simplest way is to drop the user that owns the objects with the cascade command   DROP USER username CASCADE

User · Answer

Another answer that worked for me is  credit to http   snipt net Fotinakis drop-all-tables-and-constraints-within-an-oracle-schema    BEGIN  FOR c IN  SELECT table name FROM user tables  LOOP EXECUTE IMMEDIATE   DROP TABLE       c table name       CASCADE CONSTRAINTS    END LOOP   FOR s IN  SELECT sequence name FROM user sequences  LOOP EXECUTE IMMEDIATE   DROP SEQUENCE      s sequence name   END LOOP   END    Note that this works immediately after you run it  It does NOT produce a script that you need to paste somewhere  like other answers here   It runs directly on the DB

User · Answer

To remove all objects in oracle     1  Dynamic  DECLARE CURSOR IX IS SELECT   FROM ALL OBJECTS WHERE OBJECT TYPE   TABLE   AND OWNER  SCHEMA NAME    CURSOR IY IS  SELECT   FROM ALL OBJECTS WHERE OBJECT TYPE  IN   SEQUENCE    PROCEDURE    PACKAGE    FUNCTION    VIEW   AND  OWNER  SCHEMA NAME    CURSOR IZ IS  SELECT   FROM ALL OBJECTS WHERE OBJECT TYPE IN   TYPE   AND  OWNER  SCHEMA NAME   BEGIN  FOR X IN IX LOOP    EXECUTE IMMEDIATE  DROP    X OBJECT TYPE    SCHEMA NAME    X OBJECT NAME     CASCADE CONSTRAINT     END LOOP   FOR Y IN IY LOOP    EXECUTE IMMEDIATE  DROP    Y OBJECT TYPE    SCHEMA NAME    Y OBJECT NAME    END LOOP   FOR Z IN IZ LOOP    EXECUTE IMMEDIATE  DROP    Z OBJECT TYPE    SCHEMA NAME    Z OBJECT NAME    FORCE      END LOOP  END      2 Static      SELECT  DROP TABLE       TABLE NAME       CASCADE CONSTRAINTS   FROM user tables         union ALL         select  drop    object type        object name        from user objects          where object type in   VIEW   PACKAGE   SEQUENCE    PROCEDURE    FUNCTION           union ALL         SELECT  drop             object type                          object name              force           FROM user objects         WHERE object type IN   TYPE

User · Answer

SELECT  DROP TABLE       TABLE NAME       CASCADE CONSTRAINTS    FROM user tables    user tables is a system table which contains all the tables of the user the SELECT clause will generate a DROP statement for every table you can run the script

User · Answer

If you just want a really simple way to do this   Heres a script I have used in the past  select  drop table    table name    cascade constraints   from user tables    This will print out a series of drop commands for all tables in the schema  Spool the result of this query and execute it   Source  https   forums oracle com forums thread jspa threadID 614090  Likewise if you want to clear more than tables you can edit the following to suit your needs  select  drop    object type        object name        from user objects where object type in   VIEW   PACKAGE   SEQUENCE    PROCEDURE    FUNCTION    INDEX

User · Answer

BEGIN    FOR cur rec IN  SELECT object name  object type                    FROM user objects                    WHERE object type IN                                TABLE                                  VIEW                                  MATERIALIZED VIEW                                  PACKAGE                                  PROCEDURE                                  FUNCTION                                  SEQUENCE                                  SYNONYM                                  PACKAGE BODY                                     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     FOR cur rec IN  SELECT                       FROM all synonyms                     WHERE table owner IN  SELECT USER FROM dual      LOOP       BEGIN          EXECUTE IMMEDIATE  DROP PUBLIC SYNONYM      cur rec synonym name        END     END LOOP  END

User · Answer

Please follow the below steps   begin   for i in  select  drop table    table name    cascade constraints  tb from user tables     loop      execute immediate i tb    end loop    commit  end  purge RECYCLEBIN

User · Answer

The easiest way would be to drop the tablespace then build the tablespace back up  But I d rather not have to do that  This is similar to Henry s except that I just do a copy paste on the resultset in my gui   SELECT    DROP     object type    object name    CASE object type       WHEN  TABLE  THEN  CASCADE CONSTRAINTS        ELSE        END  FROM user objects  WHERE    object type IN   TABLE   VIEW   PACKAGE   PROCEDURE   FUNCTION   SEQUENCE

[oracle] How to drop all user tables?

Examples related to oracle

Examples related to constraints

Examples related to sql-drop