2

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!

| improve this question | | | | |
4

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.

| improve this answer | | | | |
  • 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 May 23 '12 at 16:44
  • you're welcome. Maybe that's the reason behind my blog's name... ;-) – ik_zelf May 23 '12 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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