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:

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;