溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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::=
一、 建立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 farch2
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 farch2 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 farch2 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 farch2 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 hh34: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
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 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 hh34: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(*)
----------
18
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(*)
----------
36
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 hh34:mi:ss')
15:18:30 5 and
15:18:32 6 to_timestamp('2015-05-11 15:17:33','yyyy-mm-dd hh34: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
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 hh34:mi:ss')
15:19:59 5 and
15:20:02 6 to_timestamp('2015-05-11 15:17:33','yyyy-mm-dd hh34: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 hh34:mi:ss')
15:20:41 5 and
15:20:44 6 to_timestamp('2015-05-11 15:20:00','yyyy-mm-dd hh34: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 farch2
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 farch2 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 farch2 to scott;
Grant succeeded.
11:20:56 SCOTT@ orcl >alter table emp2 flashback archive farch2;
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 hh34: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 hh34: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。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
