Oracle Study学习之--Flashback Archive

秒速五厘米 2022-08-08 07:00 10阅读 0赞

**Oracle Study学习之--Flashback Archive**

**CREATE FLASHBACK ARCHIVE**

Purpose

Use the CREATE FLASHBACK ARCHIVE statement tocreate a flashback data archive, which provides the ability to automaticallytrack and archive transactional data changes to specified database objects. Aflashback data archive consists of multiple tablespaces and stores historicdata from all transactions against tracked tables.

Flashback dataarchives retain historical data for the time duration specified using the RETENTION parameter.Historical data can be queried using the Flashback Query AS OF clause.Archived historic data that has aged beyond the specified retention period isautomatically purged.

Flashback dataarchives retain historical data across data definition language (DDL) changesto tables enabled for flashback data archive. Flashback data archives supportsa lot of common DDL statements, including some DDL statements that alter tabledefinitions or incur data movement. DDL statements that are not supportedresult in error ORA-55610.

Syntax

*create\_flashback\_archive*::=

![Center][]

![Center 1][]

![Center 2][]

**一、     建立flashbackarchive**

**1、建立flashback专用表空间**

14:33:13 SYS@ orcl >create tablespaceflash\_tbs

14:33:26  2  datafile'/u01/app/oracle/oradata/orcl/flash\_tbs01.dbf' size 100m;

Tablespace created.

Elapsed: 00:00:22.03

**2、建立flashback archive**

14:36:16 SYS@ orcl >create flashbackarchive farch1

14:36:54  2  tablespace flash\_tbs

14:36:58  3  quota 20m

14:37:01  4  retention 1 month;

Flashback archive created.

**3、修改flashback archive属性**

14:37:09 SYS@ orcl >alter flashbackarchive farch1 set default;

Flashback archive altered.

Elapsed: 00:00:00.15

**4、查看flashback archive属性**

14:40:13 SYS@ orcl >colFLASHBACK\_ARCHIVE\_NAME for a20

14:40:25 SYS@ orcl >select FLASHBACK\_ARCHIVE\_NAME,RETENTION\_IN\_DAYS ,STATUS fromdba\_flashback\_archive;

FLASHBACK\_ARCHIVE\_NA RETENTION\_IN\_DAYSSTATUS

\-------------------- ------------------------

FARCH1                              30 DEFAULT

Elapsed: 00:00:00.01

14:41:54 SYS@ orcl >col QUOTA\_IN\_MB fora10

14:42:02 SYS@ orcl >select \* from dba\_flashback\_archive\_ts;

FLASHBACK\_ARCHIVE\_NA FLASHBACK\_ARCHIVE\# TABLESPACE\_NAME                QUOTA\_IN\_M

\-------------------- ------------------------------------------------ ----------

FARCH1                                1FLASH\_TBS                      20

**5、添加tablespace到flashback archive**

14:42:03 SYS@ orcl >create tablespaceflash\_tbs2

14:42:36  2  datafile'/u01/app/oracle/oradata/orcl/flash\_tbs2a.dbf' size 100m;

Tablespace created.

14:43:51 SYS@ orcl >alter flashbackarchive farch1 add tablespace flash\_tbs2;

Flashback archive altered.

Elapsed: 00:00:00.22

14:44:05 SYS@ orcl >select \* fromdba\_flashback\_archive\_ts;

FLASHBACK\_ARCHIVE\_NA FLASHBACK\_ARCHIVE\#TABLESPACE\_NAME                QUOTA\_IN\_M

\-------------------- ------------------------------------------------ ----------

FARCH1                                1 FLASH\_TBS                      20

FARCH1                                1 FLASH\_TBS2

Elapsed: 00:00:00.00

**二、在表上启用flashbackarchive**

**1、授予权限**

15:00:11 SYS@ orcl >select \* fromsystem\_privilege\_map where NAME like '%FLASHBACK%';

PRIVILEGE NAME                                       PROPERTY

\-------------------------------------------------- ----------

-243 FLASHBACK ANY TABLE                               0

-350 FLASHBACK ARCHIVE ADMINISTER                      0

Elapsed: 00:00:00.03

15:00:38 SYS@ orcl >grant flashbackarchive on farch1 to scott;

Grant succeeded.

15:04:50 SCOTT@ orcl >alter table emp2flashback archive;

Table altered.

15:11:13 SCOTT@ orcl >select \* from tab;

TNAME                          TABTYPE  CLUSTERID

\------------------------------ -----------------

BONUS                          TABLE

DEPT                           TABLE

DEPT1                          TABLE

EMP                           TABLE

EMP1                           TABLE

EMP2                           TABLE

EMP3                           TABLE

SALGRADE                       TABLE

SYS\_FBA\_DDL\_COLMAP\_75889       TABLE

SYS\_FBA\_HIST\_75889             TABLE

SYS\_FBA\_TCRV\_75889             TABLE

**三、     flashback archive 应用**

**1）   访问历史数据**

15:09:01SCOTT@ orcl >set autotrace on

15:09:08SCOTT@ orcl >r

1\* select \* from emp2 as of timestampto\_timestamp('2015-05-11 15:05:04','yyyy-mm-dd hh24:mi:ss')

EMPNO ENAME      JOB             MGR HIREDATE            SAL       COMM    DEPTNO

\-------------------- --------- ---------- ------------ ---------- ---------- ----------

7566 JONES      MANAGER         7839 02-APR-81          2975                    20

7900 JAMES      CLERK           7698 03-DEC-81           950                    30

7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30

7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30

7839 KING       PRESIDENT            17-NOV-81          5000                    10

7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30

7902 FORD       ANALYST         7566 03-DEC-81          3000                    20

7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30

7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20

7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10

7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30

7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

7369 SMITH      CLERK           7902 17-DEC-80           800                    20

7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20

14 rowsselected.

Elapsed:00:00:00.03

ExecutionPlan

\----------------------------------------------------------

Plan hashvalue: 3389285906

\----------------------------------------------------------------------------------------------------------------

\----------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |                    |    17 | 1479 |    11  (10)| 00:00:01 |       |      |

|   1 | VIEW                     |                    |   17 |  1479 |    11 (10)| 00:00:01 |       |       |

|   2 |  UNION-ALL               |                    |       |      |            |          |       |      |

|\*  3 |   FILTER                 |                    |       |      |            |         |       |       |

|   4 |    PARTITION RANGE SINGLE|                    |     1 |  113 |     2   (0)| 00:00:01 |   KEY |    1 |

|\*  5 |     TABLE ACCESS FULL    |SYS\_FBA\_HIST\_75889 |     1 |   113 |    2   (0)| 00:00:01 |   KEY |    1 |

|\*  6 |   FILTER                 |                    |       |      |            |          |       |      |

|\*  7 |    HASH JOIN OUTER       |                    |    16 | 34032 |     9 (12)| 00:00:01 |       |       |

|\*  8 |     TABLE ACCESS FULL    | EMP2               |    16 | 1584 |     5   (0)| 00:00:01 |       |      |

|\*  9 |     TABLE ACCESS FULL    |SYS\_FBA\_TCRV\_75889 |     4 |  8112 |    3   (0)| 00:00:01 |       |      |

\----------------------------------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

\---------------------------------------------------

3 -filter("TIMESTAMP\_TO\_SCN"(TIMESTAMP' 2015-05-1115:05:04.000000000')<10456170)

5 - filter("ENDSCN"<=10456170AND "ENDSCN">"TIMESTAMP\_TO\_SCN"(TIMESTAMP' 2015-05-11

15:05:04.000000000') AND("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP\_TO\_SCN"(TIMESTAMP'2015-05-11

15:05:04.000000000')))

6 -filter("STARTSCN"<="TIMESTAMP\_TO\_SCN"(TIMESTAMP'2015-05-11 15:05:04.000000000') OR "STARTSCN"

IS NULL)

7 -access("T".ROWID=CHARTOROWID("RID"(+)))

8 -filter("T"."VERSIONS\_STARTSCN" IS NULL)

9 - filter(("ENDSCN"(+) IS NULL OR"ENDSCN"(+)>10456170) AND ("STARTSCN"(+) IS NULL OR

"STARTSCN"(+)<10456170))

Note

\-----

- dynamic sampling used for this statement(level=2)

Statistics

\----------------------------------------------------------

78 recursive calls

0 db block gets

111 consistent gets

0 physical reads

0 redo size

1421 bytes sent via SQL\*Net to client

415 bytes received via SQL\*Net from client

2 SQL\*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

14 rows processed

15:09:11 SCOTT@ orcl >

**2）   生成统计报表**

15:12:07SCOTT@ orcl >select \* from emp2

15:14:53   2  versions

15:14:57   3 between timestamp

15:15:16   4 to\_timestamp('2015-05-11 15:05:04','yyyy-mm-dd hh24:mi:ss')

15:15:29   5  andmaxvalue

15:15:34   6 where ename='SCOTT';

EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM    DEPTNO

\-------------------- --------- ---------- ------------ ---------- ---------- ----------

7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20

Elapsed:00:00:00.01

15:15:45 SCOTT@ orcl >

**3）   信息生命周期管理**

15:15:45SCOTT@ orcl >select count(\*) from emp2;

COUNT(\*)

\----------

Elapsed:00:00:00.00

15:17:22SCOTT@ orcl >insert into emp2 select \* from emp2;

18 rowscreated.

Elapsed:00:00:00.00

15:17:31SCOTT@ orcl >commit;

Commit complete.

Elapsed:00:00:00.09

15:17:33SCOTT@ orcl >select count(\*) from emp2;

COUNT(\*)

\----------

Elapsed:00:00:00.00

15:17:37SCOTT@ orcl >select \* from emp2

15:18:00   2        versions

15:18:06   3   between timestamp

15:18:13   4   to\_timestamp('2015-05-11 15:05:04','yyyy-mm-dd hh24:mi:ss')

15:18:30   5   and

15:18:32   6   to\_timestamp('2015-05-11 15:17:33','yyyy-mm-dd hh24:mi:ss')

15:19:06   7   where empno=7788;

EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM    DEPTNO

\-------------------- --------- ---------- ------------ ---------- ---------- ----------

7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20

Elapsed:00:00:00.00

15:19:13SCOTT@ orcl >select \* from emp2 where empno=7788;

EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM    DEPTNO

\-------------------- --------- ---------- ------------ ---------- ---------- ----------

7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20

Elapsed:00:00:00.00

15:19:25SCOTT@ orcl >select \* from emp2

15:19:42   2 versions

15:19:45   3 between timestamp

15:19:48   4  to\_timestamp('2015-05-0915:05:04','yyyy-mm-dd hh24:mi:ss')

15:19:59   5  and

15:20:02   6 to\_timestamp('2015-05-11 15:17:33','yyyy-mm-dd hh24:mi:ss')

15:20:06   7 where empno=7788;

EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM    DEPTNO

\-------------------- --------- ---------- ------------ ---------- ---------- ----------

7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20

Elapsed:00:00:00.02

15:20:09SCOTT@ orcl >select \* from emp2

15:20:27   2 versions

15:20:30   3 between timestamp

15:20:37   4 to\_timestamp('2015-05-09 15:05:04','yyyy-mm-dd hh24:mi:ss')

15:20:41   5  and

15:20:44   6 to\_timestamp('2015-05-11 15:20:00','yyyy-mm-dd hh24:mi:ss')

15:21:01   7 where empno=7788;

EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM    DEPTNO

\-------------------- --------- ---------- ------------ ---------- ---------- ----------

7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20

Elapsed:00:00:00.01

**案例测试：**

**flashback archive：  
  
1、建立flashback archive 专用tablespace  
  
11:15:32 SYS@ orcl >create tablespace ftbs1  
11:15:40   2  datafile '/u01/app/oracle/oradata/orcl/ftbs1.dbf' size 100m;  
  
Tablespace created.  
Elapsed: 00:00:25.14  
  
2、建立flashback archive  
11:16:17 SYS@ orcl >create flashback archive farch1  
11:16:41   2  tablespace ftbs1  
11:17:14   3  quota 20m  
11:17:26   4  retention 7 day;  
  
Flashback archive created.  
Elapsed: 00:00:00.28  
  
3、查询属性  
11:17:49 SYS@ orcl >select FLASHBACK\_ARCHIVE\_NAME,RETENTION\_IN\_DAYS ,STATUS from dba\_flashback\_archive  
11:18:07   2  ;  
  
FLASHBACK\_ RETENTION\_IN\_DAYS STATUS  
\---------- ----------------- -------  
FARCH1                     7  
  
Elapsed: 00:00:00.01  
11:18:08 SYS@ orcl >select \* from dba\_flashback\_archive\_ts;  
  
FLASHBACK\_ FLASHBACK\_ARCHIVE\# TABLESPACE\_NAME                QUOTA\_IN\_M  
\---------- ------------------ ------------------------------ ----------  
FARCH1                      1 FTBS1                          20  
  
Elapsed: 00:00:00.01**

**11:18:37 SYS@ orcl >alter flashback archive farch1 set default;  
Flashback archive altered.  
Elapsed: 00:00:00.19**

**11:18:56 SYS@ orcl >select FLASHBACK\_ARCHIVE\_NAME,RETENTION\_IN\_DAYS ,STATUS from dba\_flashback\_archive;  
  
FLASHBACK\_ RETENTION\_IN\_DAYS STATUS  
\---------- ----------------- -------  
FARCH1                     7 DEFAULT**

**Elapsed: 00:00:00.01  
  
4、授权用户在table启用flashback archive  
11:19:11 SYS@ orcl >grant flashback archive on farch1 to scott;  
Grant succeeded.  
  
11:20:56 SCOTT@ orcl >alter table emp2 flashback archive farch1;  
Table altered.  
Elapsed: 00:00:00.31**

**11:21:14 SCOTT@ orcl >select \* from tab;  
  
TNAME                          TABTYPE  CLUSTERID  
\------------------------------ ------- ----------  
BONUS                          TABLE  
DEPT                           TABLE  
EMP                            TABLE  
EMP2                           TABLE  
SALGRADE                       TABLE  
T1                             TABLE  
6 rows selected.  
Elapsed: 00:00:00.01**

**11:21:46 SCOTT@ orcl >delete from emp2;  
36 rows deleted.  
Elapsed: 00:00:00.03**

**11:22:05 SCOTT@ orcl >commit;  
Commit complete.  
Elapsed: 00:00:00.19**

**11:22:08 SCOTT@ orcl >select \* from tab;  
  
TNAME                          TABTYPE  CLUSTERID  
\------------------------------ ------- ----------  
BONUS                          TABLE  
DEPT                           TABLE  
EMP                            TABLE  
EMP2                           TABLE  
SALGRADE                       TABLE  
T1                             TABLE  
6 rows selected.  
Elapsed: 00:00:00.00**

**11:22:12 SCOTT@ orcl >select \* from emp2;  
no rows selected  
Elapsed: 00:00:00.00  
  
4、查询flashback archive数据  
  
默认，Oracle 优先从undo block读取历史数据，若undo block数据不存在，然后再去访问 flashback archive。  
11:24:06 SCOTT@ orcl >set autotrace on  
11:24:16 SCOTT@ orcl >select \* from emp2 as of timestamp to\_timestamp('2015-05-12 11:21:46','yyyy-mm-dd hh24:mi:ss');  
  
  
     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- ------------ ---------- ---------- ----------  
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20  
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30  
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30  
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20  
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30  
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30  
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10  
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20  
      7839 KING       PRESIDENT            17-NOV-81          5000                    10  
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30  
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20  
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30  
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20  
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10  
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20  
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30  
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30  
  
  
     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- ------------ ---------- ---------- ----------  
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20  
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20  
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30  
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30  
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20  
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30  
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30  
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10  
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20  
      7839 KING       PRESIDENT            17-NOV-81          5000                    10  
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30  
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20  
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30  
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20  
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10  
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20  
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30  
  
  
     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- ------------ ---------- ---------- ----------  
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30  
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20  
36 rows selected.  
Elapsed: 00:00:00.01  
  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 2941272003  
  
\--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------  
|   0 | SELECT STATEMENT  |      |   654 | 56898 |     9   (0)| 00:00:01 |  
|   1 |  TABLE ACCESS FULL| EMP2 |   654 | 56898 |     9   (0)| 00:00:01 |  
\--------------------------------------------------------------------------  
Statistics  
\----------------------------------------------------------  
          4  recursive calls  
          0  db block gets  
         10  consistent gets  
          0  physical reads  
          0  redo size  
       2655  bytes sent via SQL\*Net to client  
        437  bytes received via SQL\*Net from client  
          4  SQL\*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
         36  rows processed  
           
           
         从以上sql可以看到，数据是从undo data读出  
  
11:25:16 SCOTT@ orcl >set autotrace off  
11:25:23 SCOTT@ orcl >select \* from tab;  
  
  
TNAME                          TABTYPE  CLUSTERID  
\------------------------------ ------- ----------  
BONUS                          TABLE  
DEPT                           TABLE  
EMP                            TABLE  
EMP2                           TABLE  
SALGRADE                       TABLE  
SYS\_FBA\_DDL\_COLMAP\_75889       TABLE  
SYS\_FBA\_HIST\_75889             TABLE  
SYS\_FBA\_TCRV\_75889             TABLE  
T1                             TABLE  
9 rows selected.  
Elapsed: 00:00:00.01**

**6、建立新的undo tablespace，然后切换  
11:19:42 SYS@ orcl >create undo tablespace undotbs1  
11:25:57   2  datafile '/u01/app/oracle/oradata/orcl/undotbs1.dbf' size 100m;  
Tablespace created.  
Elapsed: 00:00:23.73**

**11:26:41 SYS@ orcl >show parameter undo  
  
NAME                                 TYPE        VALUE  
\------------------------------------ ----------- ------------------------------  
undo\_management                      string      AUTO  
undo\_retention                       integer     3600  
undo\_tablespace                      string      UNDOTBS2  
11:26:45 SYS@ orcl >alter system set undo\_tablespace=undotbs1;  
System altered.  
Elapsed: 00:00:00.27**

**重新启动数据库：  
11:27:20 SYS@ orcl >startup force;  
ORACLE instance started.  
  
Total System Global Area  205049856 bytes  
Fixed Size                  1335500 bytes  
Variable Size             113250100 bytes  
Database Buffers           88080384 bytes  
Redo Buffers                2383872 bytes  
Database mounted.  
Database opened.  
  
再次查询历史数据，因为切换undo tablespace，以前的undo block不能访问，所以会从flashback archive读取数据  
11:27:50 @  >conn scott/tiger  
Connected.  
11:27:58 SCOTT@ orcl >set autotrace on  
11:28:02 SCOTT@ orcl >select \* from emp2 as of timestamp to\_timestamp('2015-05-12 11:21:46','yyyy-mm-dd hh24:mi:ss');  
  
     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- ------------ ---------- ---------- ----------  
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20  
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30  
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30  
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20  
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30  
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30  
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10  
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20  
      7839 KING       PRESIDENT            17-NOV-81          5000                    10  
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30  
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20  
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30  
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20  
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10  
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20  
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30  
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30  
  
  
     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- ------------ ---------- ---------- ----------  
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20  
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20  
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30  
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30  
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20  
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30  
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30  
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10  
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20  
      7839 KING       PRESIDENT            17-NOV-81          5000                    10  
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30  
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20  
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30  
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20  
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10  
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20  
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30  
  
  
     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- ------------ ---------- ---------- ----------  
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30  
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20  
36 rows selected.  
Elapsed: 00:00:00.13  
  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 3389285906  
  
  
\----------------------------------------------------------------------------------------------------------------  
| Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
\----------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT          |                    |    34 |  2958 |    16   (7)| 00:00:01 |       |       |  
|   1 |  VIEW                     |                    |    34 |  2958 |    16   (7)| 00:00:01 |       |       |  
|   2 |   UNION-ALL               |                    |       |       |            |          |       |       |  
|\*  3 |    FILTER                 |                    |       |       |            |          |       |       |  
|   4 |     PARTITION RANGE SINGLE|                    |     1 |   113 |     3   (0)| 00:00:01 |   KEY |     1 |  
|\*  5 |      TABLE ACCESS FULL    | SYS\_FBA\_HIST\_75889 |     1 |   113 |     3   (0)| 00:00:01 |   KEY |     1 |  
|\*  6 |    FILTER                 |                    |       |       |            |          |       |       |  
|\*  7 |     HASH JOIN OUTER       |                    |    33 | 70191 |    13   (8)| 00:00:01 |       |       |  
|\*  8 |      TABLE ACCESS FULL    | EMP2               |    33 |  3267 |     9   (0)| 00:00:01 |       |       |  
|\*  9 |      TABLE ACCESS FULL    | SYS\_FBA\_TCRV\_75889 |    36 | 73008 |     3   (0)| 00:00:01 |       |       |  
\----------------------------------------------------------------------------------------------------------------  
  
  
Predicate Information (identified by operation id):  
\---------------------------------------------------  
  
  
   3 - filter("TIMESTAMP\_TO\_SCN"(TIMESTAMP' 2015-05-12 11:21:46.000000000')<10558115)  
   5 - filter("ENDSCN"<=10558115 AND "ENDSCN">"TIMESTAMP\_TO\_SCN"(TIMESTAMP' 2015-05-12  
              11:21:46.000000000') AND ("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP\_TO\_SCN"(TIMESTAMP' 2015-05-12  
              11:21:46.000000000')))  
   6 - filter("STARTSCN"<="TIMESTAMP\_TO\_SCN"(TIMESTAMP' 2015-05-12 11:21:46.000000000') OR "STARTSCN"  
              IS NULL)  
   7 - access("T".ROWID=CHARTOROWID("RID"(+)))  
   8 - filter("T"."VERSIONS\_STARTSCN" IS NULL)  
   9 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>10558115) AND ("STARTSCN"(+) IS NULL OR  
              "STARTSCN"(+)<10558115))  
  
Note  
\-----  
   - dynamic sampling used for this statement (level=2)  
  
Statistics  
\----------------------------------------------------------  
       3280  recursive calls  
          0  db block gets  
        852  consistent gets  
        202  physical reads  
        412  redo size  
       2489  bytes sent via SQL\*Net to client  
        437  bytes received via SQL\*Net from client  
          4  SQL\*Net roundtrips to/from client  
         57  sorts (memory)  
          0  sorts (disk)  
         36  rows processed  
           
         从以上执行计划可以看出，访问的是flashback archive ，而不是undo data。  
         **

[Center]: /images/20220805/d424d547cc69467fb1d5740d50c39f91.png
[Center 1]: /images/20220805/19de2adf3f24474e88e0b123f9587d9d.png
[Center 2]: /images/20220805/b36613f49e1947a1b43e99659fb11164.png