Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

in an Oracle 11.2 Database, Enterprise Edition, we are running calculations divided into 3 different "modules". These modules can be started on their own, but not parallel, so in between users are able to confirm the calculations and proceed with the next step.

Now I want to find a proper way to "save" and "restore" the tables to allow "a rollback" to the definied start before the module.

I have read the Oracle-Documentation about (garantueed) restore points but i think its way too much. Within its time of usage the process is more or less exclusive, but not garantueed. So i cannot rollback the complete database.

I found some information about the OracleWorkspaceManager but I am not sure if this tool would be something I could use.

Finally, I simply want to save and maybe restore 6 or 7 different tables. Is there an "easy" way to do this? Alternatively I am thinking of materialized views to store the information "before" each step. But that would create dependencies into the workflow I cannot image right now.

Thank you for your help.

share|improve this question
Have you looked at the Flashback table feature? – Mat Dec 11 '13 at 10:59
So you say a restorepoint is usable to restore a specific table (or more specific tables) independent of other tables, users or sessions (not on the specified tables) by using this command? The Documentation is stating some stuff about reusing the log files and i have to admit that i do not fully understand the process including the flashback_retention_target and other dependencies stated in the documentation. – resTive Dec 11 '13 at 11:19
I don't think flashback table is based on flashback database - AFAIK the main requirement is undo being available. You'll have to read the corresponding docs very carfully to know whether that's appropriate or not for your situation. Simply copying the table with CATS before you start (i.e. really old-fashion) might also be an option. – Mat Dec 11 '13 at 11:52
Using DataPump is a good way to do that. – a_horse_with_no_name Dec 11 '13 at 12:07
Guaranteed restore points are just that, they will let you restore everything back to that state. You cannot restore individual tables, it is at whole database level. In your case, you probably should think of fixing the application to perform required rollback (safe restartability). Other than that, you can still select data in individual tables as of a certain point (using flashback) and re-create that data. But is not very safe (to flashback individual tables) if you have substantial RI in your schema. I still think fixing application might turn out to be best solution if possible. – Raj Dec 11 '13 at 18:48

IF you just want to be able to restore a few tables and the volume is not to high, why not just make copies of them?

You do need to set up good auditing and security around all your tables to prevent data loss, in the event that users did manual updates on tables that are going to be restored because of a process failure or something similar.

It could be that despite that failure, the user modifications are still needed and valid. Best would be to prevent any user changes in the period where processes run that could cause the need for a restore.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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