Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
Connected as system@LOCAL_DB
SQL> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900 --默认是1440 即24*60,只保留一天的闪回数据,具体值在安装数据库实例时可以设置
undo_tablespace string UNDOTBS1
SQL> show parameter recycle NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin on
实例一:闪回sys用户下的表 SQL> select * from user_recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ---------- SQL> drop table dept; Table dropped SQL> select * from user_recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ---------- SQL>flashback table dept to before drop; --或者使用(flashback table dept to before drop rename to dept_bak;) 报如下错误:ORA-38305: 对象不在回收站中 原因:Flashback不支持sys用户与system表空间下的对象,也不能从回收站里拿到。故使用SYS或者SYSTEM用户登陆时,show recyclebin为空。 实例二:闪回非sys、system用户下的表
先删除表:
SQL> drop table DEPT_BAK;
Table dropped
SQL> select * from user_recyclebin a where a.original_name = 'DEPT_BAK';
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$BnpnsFC8SfSYdJ7Qio0k2g==$0 DEPT_BAK DROP TABLE TESTSPACE 2013-04-21:15:38:04 2013-08-25:22:11:45 13087074 YES YES 745783 745783 745783 8
SQL> flashback table DEPT_BAK to before drop;
Done
语法一:
select * from dept
VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '1' DAY AND SYSTIMESTAMP -INTERVAL '1' HOUR;
语法二:
select * from dept as of timestamp to_timestamp('2013-08-25 21:12:46','yyyy-mm-dd HH24:MI:SS');
语法三:
select * from dept as of scn 13081009;
语法四:
select * from flashback_transaction_query a where a.table_name='DEPT';