1、创建闪回数据归档
SQL> create tablespace tbs1 datafile ‘/u01/app/oracle/oradata/prod/tbs01.dbf’ size 100M;
SQL> create flashback archive fla1 tablespace tbs1 quota 10M retention 5 year;
为系统指定默认的闪回数据归档
CREATE FLASHBACK ARCHIVE DEFAULT fla2
TABLESPACE tbs1 QUOTA 10G RETENTION 2 YEAR;
或者
alter flashback archive fla2 set default;
3、对表启用闪回数据归档(系统不允许对表执行某些DDL语句)
SQL> conn sh/oracle
SQL> alter table SALES flashback archive fla1;
禁用历史记录跟踪
alter table SALES no flashback archive fla1;
4、使用闪回数据归档恢复
SQL> !date
Thu Nov 29 21:12:29 CST 2018
SQL> select count(*) from sales;
COUNT(*)
918843
SQL> delete from sales where rownum<1000;
999 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from sales;
COUNT(*)
917844
SQL> select count(*) from sales as of timestamp to_timestamp(‘20181129 21:12:00’,‘yyyy-mm-dd hh24:mi:ss’);
COUNT(*)
918843
增加闪回数据归档表空间
ALTER FLASHBACK ARCHIVE fla1ADD TABLESPACE tbs3 QUOTA 5G;
更改保留时间
ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;
删除闪回数据归档表空间
DROP FLASHBACK ARCHIVE fla1;
示例:
• 查询闪回数据归档的创建时间:
SELECT FLASHBACK_ARCHIVE_NAME, CREATE_TIME, STATUS
FROM DBA_FLASHBACK_ARCHIVE;
• 列出闪回数据归档使用的表空间:
SELECT *
FROM DBA_FLASHBACK_ARCHIVE_TS;
查看闪回数据归档的表
SELECT *
FROM DBA_FLASHBACK_ARCHIVE_TABLES;