oracle 如何恢复Oracle表中的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15298516/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to restore the data in a Oracle table?
提问by Walter Colchado
I did a terrible mistake on my work, I executed an updated query on a oracle table without the 'where' clause and everything changed on this table, I was wondering if there is any way to restore the data on a table. I know I can use Flashback, but Is there another way to do that? If you know how to make a flashback table in oracle, please let me know.
我在工作中犯了一个可怕的错误，我在没有“where”子句的情况下在 oracle 表上执行了更新的查询，并且该表上的所有内容都发生了变化，我想知道是否有任何方法可以恢复表上的数据。我知道我可以使用 Flashback，但是还有其他方法吗？如果您知道如何在 oracle 中制作闪回表，请告诉我。
I'm using Oracle 10g R2 10.2.0.1
我使用的是 Oracle 10g R2 10.2.0.1
回答by Justin Cave
First, did you commit the change? If not, you can simply issue a rollbackto revert your changes.
首先，您是否提交了更改？如果没有，您可以简单地发出 arollback来恢复您的更改。
Assuming that you did commit your changes, are other users modifying the table at the same time? Do you need to preserve the changes that others have made and only revert the changes you made in your transaction? Or can you restore the entire table to a point in time before your changes were made?
假设您确实提交了更改，其他用户是否同时修改了表？您是否需要保留其他人所做的更改并仅还原您在事务中所做的更改？或者您能否将整个表恢复到您进行更改之前的某个时间点？
If you can restore the entire table to a point in time
如果可以将整个表恢复到某个时间点
FLASHBACK TABLE <<table name>>
TO TIMESTAMP( systimestamp - interval '10' minute )
will return a table to the state it was in 10 minutes ago assuming that the UNDOnecessary to do so remains available (so you only have a limited time after making a mistake to be able to flashback that mistake). In order to issue a FLASHBACK TABLE, you also have to make sure that
将把表返回到 10 分钟前的状态，假设这样UNDO做的必要条件仍然可用（所以你在犯错后只有有限的时间能够闪回那个错误）。为了发出 a FLASHBACK TABLE，您还必须确保
- The table has enabled row movement
ALTER TABLE <<table name>> ENABLE ROW MOVEMENT - You must have
FLASHBACKprivileges on the table or theFLASHBACK ANY TABLEsystem privilege.
- 该表已启用行移动
ALTER TABLE <<table name>> ENABLE ROW MOVEMENT - 您必须具有
FLASHBACK表FLASHBACK ANY TABLE权限或系统权限。
回答by Mykhaylo Adamovych
Starting from Oracle9i R2, doesn't require specific rights
从 Oracle9i R2 开始，不需要特定权限
Revert updated columns
还原更新的列
update <table> t
set (<column1>, <column2>, ...)
= (select <column1>, <column2>, ...
from <table> as of timestamp to_timestamp('2016-07-21 09:39:20', 'YYYY-MM-DD HH:MI:SS') h
where t.<uk> = h.<uk>);
Revert deleted rows
还原已删除的行
insert into <table>
select * from <table> as of timestamp to_timestamp('2016-07-21 03:30:00', 'YYYY-MM-DD HH:MI:SS')
where <uk> not in
(select t.<uk> from <table> t);
Don't be misleaded with DB timezone and verify current time
不要被数据库时区误导并验证当前时间
select sysdate from dual;
