When using expdp to create a snapshot of an oracle schema, how do I ensure that no post-export additions are retained upon using impdp to revert?

Question

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.

Questions:

• 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?

Thanks!

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.