Oracle flushback
闪回查询: 基于时间的flash back 查询 SQL> set time on; 12:09:12 SQL> delete from firefox_t01_t where x=1; 12:09:20 SQL> commit; 12:11:05 SQL> select * from firefox_t01_t as of timestamp to_timestamp('2015-03-05 12:09:12','YYYY-MM-DD hh:mi:ss'); 基于SCN的闪回查询 SQL> select current_scn from v$database; CURRENT_SCN ----------- 5253072 SQL> insert into firefox_t01_t values(1,23); SQL> select * from firefox_t01_t; X Y ---------- ---------- 100 2000 1 23 SQL> select * from firefox_t01_t as of scn 5253072; --原理读取UNDO表空间中的回滚段中的数据; undo retention; 参数设置: 闪回版本查询:每一次事物就是一个版本 flashback versions query insert into firefox_t01_t values(i,23); declare i number; BEGIN for i in 1 .. 10 loop insert into firefox_t01_t values(i,100); commit; end loop; end; / 闪回事物查询; select operation,undo_sql from flashback_transaction_query where logon_user = 'SYS' and undo_sql like '%FIREFOX_T01%'; --like 表; select operation,undo_sql from flashback_transaction_query where logon_user='SYS' and undo_sql like '%TEST%' and xid=HEXTORAW('ASD21123134'); 闪回表: 将表闪回到历史的某个时刻 declare i number; BEGIN for i in 1 .. 10 loop insert into firefox_t01_t values(i,100); commit; end loop; end; / select rowid,x,y from firefox_t01_t; select current_scn from v$database; delete from firefox_t01_t; alter table firefox_t01_t enable row movement; --允许行移动; SQL> flashback table firefox_t01_t to scn 5258255; 闪回删除;和回收站; DDL操作是不能使用UNDO日志进行回滚; SQL > show parameter recyclebin; SQL > show recyclebin; drop table firefox_t01_t; show recyclebin; flashback table firefox_t01_t to before drop; 怎样清空回收站 drop table firefox_t01_t purge; purge recyclebin; purge table firefox_t01_t; --单独对对象进行清空; 回收站的空间管理 SQL>select username,default_tablespace from user_users; create table t_dba_object as select * from dba_objects; select sum(blocks) from dba_free_space where tablespace_Name = 'SYSTEM'; drop table t_dba_object; select sum(blocks) from dba_free_space where tablespace_Name = 'SYSTEM'; select blocks from user_segments where segment_name='asdasdasd'; 基于UNDO的flushback有时间的限制; 闪回归档 用实际的数据来记录数据的修改 然回复永不过期.专有的后台进程fdba异步的捕获数据: 默认5分钟 所改变的活跃程度调整; 闪回归档: create tablespace his_data_1 datafile '/home/opt/app/oracle/11.2.0/+data' SIZE 500M; create flashback archive default fba1 tablespace his_data_1 RETENTION 7 DAY; create flashback archive fba2 tablespace his_data_1 quota 200M RETENTION 30 DAY; 闪回归档;授权: GRANT FLASHBACK ARCHIVE ON fba1 to c##scott; GRANT FLASHBACK ARCHIVE ON fba2 to c##scott; 创建表 create table empsal_his(empno number,ename varchar2(10),sal number)flashback archive; alter table t FLASHBACK ARCHIVE fba2; SQL> update t set object_id = 100; create undo tablespace undotbs2 datafile '/home/opt/app/oracle/11.2.0/+undodata' size 100M; alter system set undo_tablespace='UNDOTBS2'; drop tablespace undotbs1 including contents and datafiles; 闪回: select distinct object_id from t as of timestamp to_timestamp ('12-NOV-2009 14:00:00','DD-MON-YYYY HH24:MI:SS'); 如何查看闪回归档: select FLASHBACK_ARCHIVE_NAME,tablespace_name,QUOTA_IN_MB from dba_flashback_archive_ts; select FLASHBACK_ARCHIVE_NAME,to_char(create_time,'YYYY-MM-DD') created, RETENTION_IN_DAYS,STATUS from dba_flashback_archive; select table_name,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS FROM dba_flashback_archive_tables; -- 闪回表 select * from dba_objects where object_id = 75485 or object_id = 75193; select object_id from dba_objects where object_name = 'T' select table_name,tablespace_name from user_tables where tablespace_name = 'his_data_1' or tablespace_name = 'hist_data_2'; 修改闪回归档的语句: alter FLASHBACK ARCHIVE fba1 modify tablespace his_data_1 quota 250M; alter FLASHBACK ARCHIVE fba1 modify RETENTION 1 DAY; drop flushback archive fba1; alter table t no flashback archive; 当数据超过设定保留期限后,会自动删除. 允许手工删除 alter flashback archive fba1 purge all; alter flashback archive fba1 purge before timestamp(systimestamp - interval '1' DAY); alter flashback archive fba1 purge before scn 528967;