Trashy Yet Effective Oracle Trick

I need to post this so I can remember it next time I screw up...

I was running an Oracle import (imp) on my local Oracle 10g XE instance today, and realized I did it incorrectly and imported everything into the SYSTEM schema. Oh crap, I can't just wipe everything out, it's my SYSTEM schema! I need surgical precision to only remove the crap and leave all the Oracle stuff unscathed.

I could go through and drop each table I created, but there were hundreds.

Then, like a tornado in a trailerpark, it hit me. An idea that walks the fine line between genius and insanity. All the new table names started with a common prefix, which for sake of discussion I will call "BLAH_". So in Squirrel I run the following query:

select 'drop table system.' || table_name || ';' from all_tables where owner = 'SYSTEM' and table_name like 'BLAH_%'

Then cut and paste the results back into the Squirrel query runner and run the results as a script.

I'm sure there's an "official" way to accomplish the same thing, but screw that, this was extremely quick and kind of fun.

Trashy yet effective.