SQL Script to empty out/cleanse a user schema

9 thoughts on “SQL Script to empty out/cleanse a user schema”

1. Digital Photography

   hey great stuff

2. Deploying and undeploying applications in jboss with a oracle 10g database can be a pain.
   I use the purge tablespace command to empty the recyclebin per tablespace

   What i really want to know, is if I can change the type-mapping of the standardjbosscmp-jdbc.xml to extend the DROP TABLE command with PURGE. That way, you don’t have to manually purge the recycle bin.

3. Jep you got a point there. Thinking of “training” data – it could help if the constraint was build-in the create statement.

4. I’m not sure that using the CREATED column makes a difference – the constraints are what would impose some rules about what to try to drop first and they are often added after the tables.

   The “cascade constraints” is there to remove the need for ordering the drops and it shouldn’t be necessary to run the script more than once.

5. Regarding Andrews comments. Drop the objects in order of the CREATED column in view ALL|DBA|USER_OBJECTS and build them in reverse order. This will help regarding multiple executing your script.

6. Within the bounderies of the problem – a “select from DBA_objects” would not suffice. A simple user can not address this view. Nethertheless i will give you some brainfood. Since Oracle database version 9i (v2?) there was a great new package which appealed me as a DBA greatly: DBMS_METADATA

   There are numerous (DBA) scripts around to handle the problem. But now there is a package which could be used to embedded it neatly in your specific solution.

   conn system/manager
   connected
   
   SQL> SELECT DBMS_METADATA.GET_DDL('USER','SCOTT') from DUAL;
   
      CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
         DEFAULT TABLESPACE "USERS"
         TEMPORARY TABLESPACE "TEMP"
   
   SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','UP7')
     2  FROM DUAL;
   
     GRANT SELECT ANY DICTIONARY TO "UP7"
     GRANT CREATE DIMENSION TO "UP7"
     GRANT CREATE ANY TYPE TO "UP7"
     GRANT CREATE ANY DIRECTORY TO "UP7"
   
   SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','UP7') FROM DUAL;
   
     GRANT READ ON DIRECTORY "DIR_DATA" TO "UP7" WITH GRANT OPTION
     GRANT WRITE ON DIRECTORY "DIR_DATA" TO "UP7" WITH GRANT OPTION
   
   SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') from DUAL;
   
     CREATE TABLE "SCOTT"."EMP"
      (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
           "ENAME" VARCHAR2(10),
           "JOB" VARCHAR2(9),
           "MGR" NUMBER(4,0),
           "HIREDATE" DATE,
           "SAL" NUMBER(7,2),
           "COMM" NUMBER(7,2),
           "DEPTNO" NUMBER(2,0) NOT NULL ENABLE,
            CONSTRAINT "EMP_PRIMARY_KEY" PRIMARY KEY ("EMPNO")
     USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
     TABLESPACE "USERS"  ENABLE,
            CONSTRAINT "EMP_SELF_KEY" FOREIGN KEY ("MGR")
             REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE,
            CONSTRAINT "EMP_FOREIGN_KEY" FOREIGN KEY ("DEPTNO")
             REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
      ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
     TABLESPACE "USERS"
   
   

   Just some simple examples…to show how powerfull this could be…and its not only limited to dba privs.

7. On 10g and the Recycle Bin (also see: Flashback Drop)
   Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.

   This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.

   Recycle Bin

   A recycle bin contains all the dropped database objects until,

   – You permanently drop them with the PURGE command.
   – Recover the dropped objects with the UNDROP command.
   – There is no room in the tablespace for new rows or updates to existing rows.
   – The tablespace needs to be extended.

   You can view the dropped objects in the recycle bin from two dictionary views:

   – user_recyclebin – lists all dropped user objects
   – dba_recyclebin – lists all dropped system-wide objects

   Some statements in relation to the recycle bin:

   Because of the purge keyword, the table test is not just dropped (ending up in the bin) but permanently removed instead
   SQL> drop table test purge;

   This statement purges an already dropped table from the bin:
   SQL> purge table RB$$42514$TABLE$0;
   Table purged.

   This statement purges all objects from tablespace users in the recycle bin:

   SQL> purge tablespace users;
   Tablespace purged.

   SQL> purge recyclebin;
   Recyclebin purged.

8. The cascade constraints is an important improvement.

   However, I feel that a much better, smarter and SQL*Plus independent approach is using EXECUTE IMMEDIATE in a PL/SQL Block.

   Something like this??

   declare
     cursor c_obj( b_owner in varchar2)
     is
       SELECT object_type
       ,      object_name
       ,      owner
       FROM   dba_objects
       where  owner = b_owner
       and    object_type not in ( 'PACKAGE BODY','TYPE BODY', 'INDEX', 'TRIGGER', 'UNDEFINED', 'LOB')
       and    ( object_type <> 'TABLE' or object_name not like 'BIN%')  -- do not attempt to drop alrea
       ;
     l_execute varchar2(2000);
   begin
     for r_obj in c_obj( b_owner => upper('&schemaname')) loop
        l_execute:= 'drop '||r_obj.object_type||' '||r_obj.owner||'.'||r_obj.object_name;
        if r_obj.object_type = 'TABLE'
        then
          l_execute:= l_execute || ' CASCADE CONSTRAINTS';
        end if;
        EXECUTE IMMEDIATE l_execute;
     end loop;
   end;
   

9. I use this slightly improved script:
   SELECT ‘drop ‘ ||object_type || ‘ ‘|| owner||’.’ || object_name || ‘;’
   FROM dba_objects
   where owner in (‘USER1’, ‘USER2’)
   and object_type not in ( ‘PACKAGE BODY’, ‘INDEX’, ‘TABLE’, ‘TRIGGER’, ‘UNDEFINED’)
   union
   SELECT ‘drop ‘ || object_type || ‘ ‘ || owner||’.’||object_name || ‘ cascade constraints;’
   FROM dba_objects
   where owner in (‘USER1’, ‘USER2’)
   and object_type = ‘TABLE’;

   I forget what ‘UNDEFINED’ is for, but I needed it for a particular schema. This script doesn’t drop DB links too well because it prefixes them with “user.”

Comments are closed.