I'm continuing on my theme of a wish list for 12c (or beyond), and this time I'm taking aim at destroying stuff.
Juggling development and test areas, every so often it is tempting to wipe the slate clean and start with a fresh schema. The simple way to do that is with a DROP USER blah CASCADE
It would be nice to have something one step below the 'nuclear' option. I'm thinking
DROP ALL TABLES UNDER SCHEMA blah
plus similar options for dropping sequences, views, procedures, triggers and so on. The user/schema continues to exist, with all their privileges and defaults. But you get to clear out the objects the schema owns.
Yes, you can script this. The trick is to disable all the referential integrity constraints first, so that you can drop the tables without worrying about the dependencies.
Cross-schema constraints could still be a problem, as could firing DDL triggers. I'm open to alternative mechanisms. Perhaps DBMS_METADATA could generate a script for dropping objects.
3 comments:
"..disable all the referential integrity constraints first.." - referential partitioning makes that a bit tricky.
If schemas were properly supported, it'd be as easy as:
DROP SCHEMA ;
while leaving the associated login active.
Yes, definitely it'd be welcome.
I have this problem every month, during test and acceptance refreshes. RPITA...
For that matter, a rename user command would be useful. I could dup a db, rename the user, transport to various places, avoiding app's special requirements that forces datapump for hours on end.
word: chmena
Post a Comment