I want to create a snapshot of an Oracle schema that I can revert back to after testing. Impdp and expdp seem to be the best fit as far as how I'd like to accomplish it, but the problem is that impdp only overwrites the existing schema without getting rid of any data that is not included in the dumpfile.

I could "DROP USER megan CASCADE," but wouldn't that involve using SQL *Plus and quitting after the drop before executing impdp? Is that even possible?

I tried that, and it didn't work. I may be doing it incorrectly, as I have about two days of experience working with and researching databases, but I haven't been able to find a way to drop the user without using SQL *Plus.


  • How can I clean my schema so that my import is an accurate snapshot?
  • Is using "DROP USER" a viable option?
  • Would that even accomplish what I'd like to do?
  • Are there other options?


1 Answer 1


As always with Oracle, there are many ways to reach your goal. You might want to check out flashback database. This enables you to restore your database in a previously saved state and might be the easiest in your case. Using Flashback Database and Restore Points

Drop user cascade could also do the trick, followed by impdb as you described. What was the problem with the drop user action?

Try flashback database.

  • Thanks for the help! I'm embarrassed to admit that the drop user problem was a matter of syntax and priviliges :P Everything is working now.
    – Megan
    Commented May 23, 2012 at 16:44
  • you're welcome. Maybe that's the reason behind my blog's name... ;-)
    – user953
    Commented May 23, 2012 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.