flashback data archive

A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports. 以下实例操作:[@more@]

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

授予用户创建flashback archive的权限
SQL> select name from system_privilege_map where name like 'FLA%';
NAME
----------------------------------------
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ANY TABLE

SQL> grant FLASHBACK ARCHIVE ADMINISTER to ww;

创建flashback archive
SQL> create flashback archive ww_arch1
2 tablespace users
3 quota 10M
4 retention 1 day;

创建缺省的闪回归档区
SQL> conn / as sysdba
SQL> create flashback archive default ww_arch2
2 tablespace users
3 quota 10M
4 retention 1 day;

Flashback archive created.

SQL> alter flashback archive ww_arch1 set default;
拥有flashback archive administer权限只能创建一般的闪回归档区，
默认的flashback archive只能sysdba用户才能创建和修改。

启用表的跟踪
SQL> alter table t_emp flashback archive;

Table altered.

-- alter table t_emp no flashback archive; 反向操作

插入测试数据
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-----------------
20120419 11:21:36

SQL> select count(1) from t_emp;

COUNT(1)
----------
2048

SQL> insert into t_emp select * from t_emp where rownum<11;

10 rows created.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-----------------
20120419 11:23:07

SQL> select count(1) from t_emp as of timestamp to_timestamp('20120419 11:21:36','yyyymmdd hh24:mi:ss');

COUNT(1)
----------
2048

SQL> /

COUNT(1)
----------
2048

使用sys 切换在线日志两个循环之后，还能查到数据
SQL> /

COUNT(1)
----------
2048

SQL>
SQL> /

COUNT(1)
----------
2048

系统中有哪些表启用了这个特性 ?
select table_name,flashback_archive_name from dba_flashback_archive_tables;

启用了跟踪的表，不能做除了添加列之外的DDL操作
SQL> drop table t_emp;
drop table t_emp
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

清除闪回归档区的数据
conn / as sysdba
alter flashback archive ww_arch1
purge before timestamp (systimestamp - interval '1' hour);
alter flashback archive ww_arch1
purge before scn 1450112 ;
alter flashback archive ww_arch1 purge all;

参考文档
Oracle® Database Advanced Application Developer's Guide
11g Release 2 (11.2)
Part Number E25518-04
12 Using Oracle Flashback Technology

如果是11gR1
Oracle® Database Advanced Application Developer's Guide
11g Release 1 (11.1)
Part Number B28424-03
13 Using Oracle Flashback Technology

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271063/viewspace-1057945/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271063/viewspace-1057945/