1、准备环境

1）、创建闪回数据归档使用的表空间

SQL> create tablespace fla_tbs1

datafile '/u01/app/oracle/oradata/ORCL/fla_tbs01.dbf'

size 10M reuse autoextend on next 640K maxsize 500M

extent management local segment space management auto; 

Tablespace created.

2）、创建一个数据归档的管理用户，并授予相关权限

SQL> create user archive_admin identified by oracle

  2  default tablespace fla_tbs1

  3  temporary tablespace temp

  4  account unlock; 

User created. 

SQL> GRANT ALTER SESSION TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CREATE DATABASE LINK TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CREATE SEQUENCE TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CREATE SESSION TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CREATE SYNONYM TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CREATE VIEW TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT UNLIMITED TABLESPACE TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT EXECUTE ON SYS.DBMS_STATS TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> GRANT CONNECT, RESOURCE TO ARCHIVE_ADMIN;

Grant succeeded. 

SQL> grant flashback archive administer to archive_admin;

2、创建数据归档

SQL> conn archive_admin/oracle

Connected. 

SQL> show user

USER is "ARCHIVE_ADMIN" 

SQL> create flashback archive fla1

  2  tablespace fla_tbs1

  3  quota 10M       

  4  retention 1 year; 

Flashback archive created.

3、授予scott用户使用数据归档fla1的权限

SQL> grant flashback archive on fla1 to scott;

Grant succeeded.

4、连接scott用户，配置emp表使用数据归档。

SQL> conn scott/tiger

Connected. 

SQL> alter table emp flashback archive fla1;

Table altered.

注意：如果emp使用另外的数据归档，则报错。

5、做些DML操作

SQL> select * from emp where empno=7902;

EMPNO  ENAME  JOB    MGR HIREDATE      SAL    COMM   DEPTNO

---------- ---------- ---------  ---------- ---------     ---------- ---------- ----------

7902   FORD   ANALYST    7566   03-DEC-81  4000          20

SQL> update emp set sal=sal+1000 where empno=7902;

1 row updated. 

SQL> commit;

Commit complete. 

SQL> update emp set sal=sal+1000 where empno=7902;

1 row updated. 

SQL> commit;

Commit complete. 

SQL> update emp set sal=sal+1000 where empno=7902;

1 row updated. 

SQL> commit;

Commit complete. 

SQL> select * from emp where empno=7902; 

EMPNO  ENAME  JOB    MGR HIREDATE      SAL    COMM   DEPTNO

---------- ---------- ---------  ---------- ---------     ---------- ---------- ----------

7902   FORD   ANALYST    7566   03-DEC-81  7000          20

查询数据归档的内部表名。

SQL> select * from user_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME

------------------------------ ------------------------------

FLASHBACK_ARCHIVE_NAME

--------------------------------------------------------------------------------

ARCHIVE_TABLE_NAME                                    STATUS

----------------------------------------------------- --------

EMP                            SCOTT

FLA1

SYS_FBA_HIST_73181                                    ENABLED

6、通过更换undo表空间，已达到清除原undo表空间中的‘旧’数据。

SQL> conn / as sysdba

Connected.

SQL> show parameter undo_tablespace

NAME                            TYPE          VALUE

------------------------------------             -----------       ------------------------------

undo_tablespace                    string     UNDOTBS1

SQL> create undo tablespace undotbs2

  2  datafile '/u01/app/oracle/oradata/ORCL/undotbs02.dbf'

  3  size 100M reuse autoextend on;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> drop tablespace UNDOTBS1;

Tablespace dropped.

SQL> show parameter undo_tablespace
NAME                            TYPE          VALUE

------------------------------------             -----------       ------------------------------

undo_tablespace                    string     UNDOTBS2

7、使用闪回查询，查询过去的数据。

SQL> select * from emp

  2  as of timestamp (systimestamp - interval '18' minute)

  3  where empno=7902;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7902 FORD       ANALYST         7566 03-DEC-81       7000

        20

SQL> select * from emp

  2  as of timestamp (systimestamp - interval '19' minute)

  3  where empno=7902;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7902 FORD       ANALYST         7566 03-DEC-81       6000

        20

SQL> select * from emp

  2  as of timestamp (systimestamp - interval '20' minute)

  3  where empno=7902;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7902 FORD       ANALYST         7566 03-DEC-81       4000

        20