Oracle10g闪回总结

Oracle 10g闪回总结 1.flashback query （undo） select current_scn from v$database; select * from t as of scn scn号;

Oracle 10g闪回总结

1.flashback query （undo）
select current_scn from v$database;
select * from t as of scn scn号;
时间与scn转换函数：
scn_to_timestamp()
to_timestamp()
timestamp_to_scn()

2.flashback table(undo)
select row_movement from dba_tables where table_name='T' and owner='TEST';
alter table t enable row movement;
flashback table t to scn scn号;

3.flashback versions query(undo)
select * from t versions between scn minvalue and maxvlaue
order by versions_startscn;

4.flashback transaction query（undo）
select * from flashback_transaction_query where xid='事务id';
事务id对应到flashback versions query中的versions_xid，这样可以找到undo_sql,对误操作撤消。

5.flashback drop
drop table t;
show recyclebin
select * from dba_recyclebin;
flashback table t to before drop;
select * from dba_indexes where table_name='T';
如果有索引，索引同样会被闪回，但是索引名称会发生变化，使用
alter index old_index_name rename to new_index_name;更改索引名称。
t表上的触发器也同样如些。

彻底删除表
drop table t purge;
清回收站
purge recyclebin;

是否启用recyclebin
show parameter recyclebin;