oracle 恢复已删除的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15332951/
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
Recovering deleted Records
提问by KTB
I have accdently deleted some rows in a table and did the commit too. Now I want to recover them.
我不小心删除了表中的一些行并且也做了提交。现在我想恢复它们。
The DB I'm using is Oracle 11g R2.
我使用的数据库是 Oracle 11g R2。
I used the following query to get deleted records:
我使用以下查询来获取已删除的记录：
SELECT * FROM MY_TABLE AS OF TIMESTAMP ('13-MAR-11 8:50:58','DD-MON-YY HH24: MI: SS')
But while executing it gives an error saying:
但是在执行时会报错：
Error at Command Line:3 Column:75
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
But I couldn't figure the problem in this queury.
但我无法弄清楚这个查询中的问题。
Can anyone pls help?
任何人都可以帮忙吗？
回答by Mat
That requires an actual timestamp (or date), you're passing a pair of values.
这需要一个实际的时间戳（或日期），您正在传递一对值。
Try:
尝试：
SELECT * FROM MY_TABLE
AS OF TIMESTAMP TO_DATE('13-MAR-11 08:50:58','DD-MON-YY HH24:MI:SS')
(Your time format specifier isn't correct either and doesn't match your date string.)
（您的时间格式说明符也不正确，并且与您的日期字符串不匹配。）
回答by ParnassusData
for example :
例如 ：
SELECT * FROM EMP AS OF TIMESTAMP
TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';
But flashback query may fail with ORA-1555 , other option :
但是闪回查询可能会因 ORA-1555 而失败，其他选项：
Logminer
登录
if Oracle supplement log is enabled , you can get undo sql for your delete statement
如果启用了 Oracle 补充日志，您可以获得删除语句的 undo sql 。
-- switch again logfile to get a minimal redo activity alter system switch logfile;
-- mine the last written archived log
exec dbms_logmnr.add_logfile('archivelog/redologfile', options =>dbms_logmnr.new);
exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
select operation, sql_redo from v$logmnr_contents where seg_name = 'EMP';
Oracle PRM-DUL
Oracle PRM-DUL
PRM-DUL will be last option. Even deleted row piece in Oracle block is always just marked row flag with deleted mask, the row piece still can be read via scan Oracle data block . PRM-DUL can scan the whole table , find out every record/row piece marked deleted and write out to flat file.
PRM-DUL 将是最后的选择。即使在 Oracle 块中删除的行块总是只是用删除掩码标记行标志，行块仍然可以通过扫描 Oracle 数据块读取。PRM-DUL 可以扫描整个表，找出每个标记为删除的记录/行块并写出到平面文件。
