Tuesday, November 15, 2011

Wish List II - Ultimate Destruction

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:

Timo Raitalaakso said...

"..disable all the referential integrity constraints first.." - referential partitioning makes that a bit tricky.

Noons said...

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...

Joel Garry said...

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