oracle 从oracle表中恢复已删除的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23334495/
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 rows from oracle table
提问by santhosha
Is this possible to recover the deleted rows from oracle table? My data is stored in a table MANUAL_TRANSACTIONS. Schema name is CCO.I have accidentally deleted some 500 Thousands rows in a table and did the commit too. Now I want to recover them.I am using Oracle 11g R2.Thanks
这可以从oracle表中恢复已删除的行吗？我的数据存储在表 MANUAL_TRANSACTIONS 中。架构名称是 CCO。我不小心删除了表中的大约 50 万行，并且也进行了提交。现在我想恢复它们。我使用的是 Oracle 11g R2。谢谢
回答by Ravi.
You can recover the details using Oracle Flashback Query. You could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database. Here's the sample query:
您可以使用 Oracle Flashback Query 恢复详细信息。您可以查询删除前某个时间的表内容，以找出丢失的数据，并在适当的情况下将丢失的数据重新插入数据库中。这是示例查询：
select * from MANUAL_TRANSACTION as of timestamp to_timestamp('28-APR-2014 12:30:00', 'DD-MON-YYYY HH:MI:SS') where ' clause based on your deleted data';
Source: http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr002.htm
来源：http: //docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr002.htm
回答by v8-E
answers are already given just what i learned form above . FLASHBACK can only be done by DBA( I guess ) but we can use below query
答案已经给出了我从上面学到的东西。FLASHBACK 只能由 DBA 完成（我猜）但我们可以使用以下查询
Insert into MANUAL_TRANSACTIONS
(SELECT * FROM MANUAL_TRANSACTIONS AS OF
TIMESTAMP TO_TIMESTAMP('2018-07-23 06:41:59', 'YYYY-MM-DD HH:MI:SS'));
or you can go for this query for one day records
或者你可以去查询一天的记录
Insert into MANUAL_TRANSACTIONS
(SELECT * FROM MANUAL_TRANSACTIONS AS OF
TIMESTAMP TO_TIMESTAMP('2018-07-23', 'YYYY-MM-DD'));
回答by Dhananjay Nandgaonkar
select * from MY_TABLE as of timestamp to_timestamp('04-MAY-2017 12:30:00', 'DD-MON-YYYY HH:MI:SS') where ID=1822904; --- 12Hr Clock
Above query works for me. You can even look for 24Hr timeframe using below query
以上查询对我有用。您甚至可以使用以下查询查找 24 小时时间范围
select * from MY_TABLE as of timestamp to_timestamp('04-MAY-2017 13:30:00', 'DD-MON-YYYY HH24:MI:SS') where ID=1822904;
回答by ik_zelf
Yes, you can, use flash back query. Using Oracle Flashback Query (SELECT AS OF)This assumes that the undo tablespace was big enough, with enough undo retention. If the undo is already freed, you might need to perform a restore and recovery, in a clone database and copy the data to the original database. Also check TSPITR, TableSpace Point In Time Recovery. This is only possible if your database runs in archivelog mode and has a backup available.
是的，您可以，使用闪回查询。 使用 Oracle 闪回查询 (SELECT AS OF)这假设撤消表空间足够大，有足够的撤消保留。如果撤消已被释放，您可能需要在克隆数据库中执行还原和恢复并将数据复制到原始数据库。还要检查 TSPITR、TableSpace Point In Time Recovery。这只有在您的数据库以归档日志模式运行并且有可用备份时才有可能。
回答by Lukasz Szozda
If you have backup and Oracle 12c you could use Table Point In Time Recovery(PITR):
如果您有备份和 Oracle 12c，则可以使用Table Point In Time Recovery（PITR）：
RECOVER TABLE 'SCHEMA'.'TAB_NAME'
UNTIL TIME xxxxyyy
AUXILIARY DESTINATION '/u01/aux'
REMAP TABLE 'SCHEMA'.'TAB_NAME':'TAB_NAME_PREV';
Your data at that point in time will be available:
您在那个时间点的数据将可用：
SELECT * FROM SCHEMA.TAB_NAME_PREV;
回答by sadasivam
INSERT INTO TABLE_NAME(SELECT * FROM TABLE_NAME AS OF TIMESTAMP(SYSDATE - 4/24)
INSERT INTO TABLE_NAME(SELECT * FROM TABLE_NAME AS OF TIMESTAMP(SYSDATE - 4/24)
回答by A Nice Guy
FLASHBACK TABLE <TABLE_NAME> TO TIMESTAMP(TO_DATE('27-APR-2014 23:59:59','DD-MON-YYYY HH24: MI: SS'));
Restores the data in the table to the given time(provided the table was not truncated).
将表中的数据恢复到给定时间（前提是表没有被截断）。
In your case:
在你的情况下：
FLASHBACK TABLE MANUAL_TRANSACTIONS TO TIMESTAMP(TO_DATE('27-APR-2014 23:59:59','DD-MON-YYYY HH24: MI: SS'));
FLASHBACK TABLE MANUAL_TRANSACTIONS TO TIMESTAMP(TO_DATE('27-APR-2014 23:59:59','DD-MON-YYYY HH24: MI: SS'));
回答by Rajan
Use this query,
使用这个查询，
Insert into MANUAL_TRANSACTIONS
(SELECT * FROM MANUAL_TRANSACTIONS AS OF
TIMESTAMP TO_TIMESTAMP('2014-04-27 11:59:59 PM', 'YYYY-MM-DD HH:MI:SS PM'))
回答by ParnassusData
There are some options:
有一些选择：
Flashback Query as
create table before_delete as select * from Table as of TIMESTAMP XX;
Logminer if Oracle supplement log is enabled , you can get undo sql for your delete statement
闪回查询为
create table before_delete as select * from Table as of TIMESTAMP XX;
Logminer 如果启用了 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 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.
- Oracle PRM-DUL 将是最后的选择。即使在 Oracle 块中删除的行块也总是只是用已删除的掩码标记行标志，该行块仍然可以通过扫描 Oracle 数据块读取。PRM-DUL 可以扫描整个表，找出每个标记为删除的记录/行块并写出到平面文件。
回答by ParnassusData
what you may try is :
你可以尝试的是：
- flashback query, available from oracle 10g , may failed with ora-01555 snapshot too old
- redo logminer , mine redo and may find undo sql
- prm-dul tool ( a commercial recovery tool for oracle), which can scan oracle block and find even deleted row piece
- 闪回查询，可从 oracle 10g 获得，可能因 ora-01555 快照太旧而失败
- redo logminer , mine redo 并且可能会发现undo sql
- prm-dul工具（oracle的商业恢复工具），可以扫描oracle块，甚至可以找到被删除的行块
