通过查询根据时间戳被删除掉的数据,在回滚段中查找,若数据太早的回滚段被冲掉就会报错,不过最近的一般都能找到(假如有张表叫:test): select versions_xid xid, versions_operation op, versions_startscn startscn, versions_endscn endscn, rowid, test.* from test versions between TIMESTAMP to_timestamp('2010-04-30 12:20:00','YYYY-MM-DD HH24:MI:SS') and maxvalue;查找到对应的时间戳后可以根据时间戳进行那个时间点的数据恢复: ALTER TABLE test ENABLE ROW MOVEMENT;--设置可以进行ROWID迁移 FLASHBACK TABLE test to TIMESTAMP to_timestamp('2010-04-30 12:20:00','YYYY-MM-DD HH24:MI:SS'); ALTER TABLE test DISABLE ROW MOVEMENT;
单独你的语句没有错,仔细看看是写法的问题不 既然知道scn 那就用时间闪回看看 alter table client enable row movement select * from client as of timestamp to_timestamp(时间,'yyyy-mm-dd hh24:mi:ss')
换一下用SCN=29389对应的时间戳timestamp来闪回呢 select SCN_TO_TIMESTAMP(29389) as ts from dual;
select versions_xid xid,
versions_operation op,
versions_startscn startscn,
versions_endscn endscn,
rowid,
test.*
from test versions between TIMESTAMP
to_timestamp('2010-04-30 12:20:00','YYYY-MM-DD HH24:MI:SS') and maxvalue;查找到对应的时间戳后可以根据时间戳进行那个时间点的数据恢复:
ALTER TABLE test ENABLE ROW MOVEMENT;--设置可以进行ROWID迁移
FLASHBACK TABLE test
to TIMESTAMP to_timestamp('2010-04-30 12:20:00','YYYY-MM-DD HH24:MI:SS');
ALTER TABLE test DISABLE ROW MOVEMENT;
既然知道scn 那就用时间闪回看看
alter table client enable row movement
select * from client as of timestamp to_timestamp(时间,'yyyy-mm-dd hh24:mi:ss')
select SCN_TO_TIMESTAMP(29389) as ts from dual;