MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Cleaning up a Schema

with 6 comments

My students wanted a simple way to cleanup a development schema. So I wrote the following anonymous block PL/SQL program, which also manages the fact that Oracle Database 12c doesn’t completely drop system-generated sequences in active sessions.

The new identity columns in Oracle Database 12c create system-generated sequences, which you must purge from the recycle bin. If you don’t a generic script, like the following raises:

ORA-32794: cannot DROP a system-generated SEQUENCE

Here’s the script that cleans up an Oracle schema:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            ORDER BY object_type DESC) LOOP
 
    /* Drop types in descending order. */
    IF i.object_type = 'TYPE' THEN
 
      /* Drop type and force operation because dependencies may exist. Oracle 12c
         also fails to remove object types with dependents in pluggable databases
         (at least in release 12.1). Type evolution works in container database
         schemas. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' FORCE';
 
    /* Drop table tables in descending order. */
    ELSIF i.object_type = 'TABLE' THEN
 
      /* Drop table with cascading constraints to ensure foreign key constraints
         don't prevent the action. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';
 
      /* Oracle 12c ONLY: Purge the recyclebin to dispose of system-generated
         sequence values because dropping the table doesn't automatically 
         remove them from the active session.
         CRITICAL: Remark out the following when working in Oracle Database 11g. */
      EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';
 
    ELSIF i.object_type = 'LOB' OR i.object_type = 'INDEX' THEN
 
      /* A system generated LOB column or INDEX will cause a failure in a
         generic drop of a table because it is listed in the cursor but removed
         by the drop of its table. This NULL block ensures there is no attempt
         to drop an implicit LOB data type or index because the dropping the
         table takes care of it. */
      NULL;
 
    ELSE
 
      /* Drop any other objects, like sequences, functions, procedures, and packages. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
 
    END IF;
  END LOOP;
END;
/

As noted by Marat, you can simplify the drop of the tables by simply appending a PURGE clause to the DROP TABLE statement.

16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    /* Drop table tables in descending order. */
    ELSIF i.object_type = 'TABLE' THEN
 
      /* Drop table with cascading constraints to ensure foreign key constraints
         but you need to purge system-generated constraints. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS PURGE';
 
    ELSE
 
      /* Drop any other objects, like sequences, functions, procedures, and packages. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
 
    END IF;
  END LOOP;
END;
/

As always, I hope this helps a few people.

Written by maclochlainn

January 17th, 2014 at 1:19 am

6 Responses to 'Cleaning up a Schema'

Subscribe to comments with RSS or TrackBack to 'Cleaning up a Schema'.

  1. Why don’t you use?

    EXECUTE IMMEDIATE
      'DROP '||i.object_type||' '||i.object_name||' '||
      'CASCADE CONSTRAINTS PURGE';

    for tables?

    Marat

    21 Jan 14 at 8:25 am

  2. Marat,

    Great question, I wanted to highlight they were left. However, I’ve updated the post to include your comment.

    Thanks,
    Michael

    maclochlainn

    21 Jan 14 at 11:28 am

  3. Unfortunately, the original script didn’t take into account implicit indexes or LOB columns. I’ve added an ELSIF block to take care of those.

    29
    30
    31
    32
    33
    34
    35
    36
    37
    
     
        ELSIF i.object_type = 'LOB' OR i.object_type = 'INDEX' THEN
     
          /* A system generated LOB column or INDEX will cause a failure in a
             generic drop of a table because it is listed in the cursor but removed
             by the drop of its table. This NULL block ensures there is no attempt
             to drop an implicit LOB data type or index because the dropping the
             table takes care of it. */
          NULL;

    maclochlainn

    8 Feb 14 at 4:50 pm

  4. This is awesome, thanks!!! I had to add a clause for object type=JOB:

        ELSIF i.object_type='JOB' THEN
          EXECUTE IMMEDIATE 'exec dbms_scheduler.drop_job ('||i.object_name||')';

    tiff

    9 Sep 14 at 5:35 am

  5. Actually, above doesn’t work.

    I used below instead:

    — elseif table

        ELSIF i.object_type='JOB' THEN
     
                DBMS_SCHEDULER.DROP_JOB (i.object_name);
     
        ELSE
     
         -- else drop all others
     
        END IF;
      END LOOP;
    END;
    /

    Thanks again!!

    tiff

    12 Sep 14 at 10:26 am

  6. Tiff, I wondered about that but was on the road last month and didn’t have the time to check. ;-)

    maclochlainn

    13 Sep 14 at 12:07 am

Leave a Reply