--检查权限
SELECT
*
FROM
dba_sys_privs
WHERE
privilege LIKE '%FLASH%'; --设置权限
GRANT dba TO testuser;--设置dba权限
GRANT flashback archive administer TO testuser; --设置闪回数据归档权限--创建表空间
/*
create tablespace flash_tbs1
logging
datafile 'D:\app\flashdata\flash_tbs1.dbf'
size 50m
autoextend on
next 10m maxsize 400m
extent management local;
*/ --创建闪回归档
CREATE flashback archive flash1 tablespace flash_tbs1 quota 10M retention 5 year; -- 设置为默认的闪回归档
ALTER flashback archive flash1
SET DEFAULT; --查看闪回归档设置的状态
SELECT
flashback_archive_name,
status
FROM
dba_flashback_archive; --设置table闪回归档
ALTER TABLE Table1 flashback archive flash1;
ALTER TABLE Table2 flashback archive flash1; --查询开启闪回归档的Table
SELECT
*
FROM
dba_flashback_archive_tables; 查询结果
Table1 SYSTEM FLASH1 SYS_FBA_HIST_73432 ENABLED
Table2 SYSTEM FLASH1 SYS_FBA_HIST_73542 ENABLED --查看T_ORG_DEPT表的历史变更记录
SELECT
*
FROM
SYS_FBA_HIST_73542 --查数据库中所有的闪回数据归档
SELECT
flashback_archive_name,
retention_in_days
FROM
dba_flashback_archive; --查有关闪回数据归档所使用的表空间的信息
SELECT
flashback_archive_name,
tablespace_name,
quota_in_mb
FROM
dba_flashback_archive_ts; --查询当前scn号
SELECT
current_scn
FROM
v$database; 查询结果
1043955
1043958
1044070
1044700 --依据scn号查询历史数据
SELECT
*
FROM
T_ORG_DEPT AS OF scn 1044070; --依据时间戳查询历史数据，不能大于当前时间。
SELECT
*
FROM
T_ORG_DEPT AS OF TIMESTAMP to_timestamp( '20180221 00:00:00', 'yyyymmdd hh24:mi:ss' ); --清除2天前的
ALTER flashback archive flash1 purge before timestamp ( systimestamp - interval '' day ); --清除scn号前的立即
ALTER flashback archive flash1 purge before scn 1044700; --查询归档的数据，如果清空全部则无法查询到历史归档数据
SELECT
*
FROM
SYS_FBA_HIST_73542
--多表测试
SELECT
Table1.NAME,
Table2.NAME
FROM
Table1 AS OF TIMESTAMP to_timestamp( '20180221 00:00:00', 'yyyymmdd hh24:mi:ss' )
INNER JOIN Table3 ON Table1.ID = Table3.PERSONID
INNER JOIN Table2 AS OF TIMESTAMP to_timestamp( '20180221 00:00:00', 'yyyymmdd hh24:mi:ss' ) ON Table3.DEPTID = Table2.ID
WHERE
Table1.id IN ( '', '3' );
参考
http://blog.csdn.net/guoyjoe/article/details/9199963