Cleaning up a Schema
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; / |
Don’t run this version if you’ve provisioned an APEX Workspace in the Oracle Schema. I’ve got an updated version of the script for APEX 4.0. As always, I hope this helps a few people.
Subscribe via RSS