闪回数据归档的实验-oracle total recall(二)
nt string AUTO
undo_retention integer 900
undo_tablespace string test1_undo
清除缓冲区数据
21:58:19 SQL> alter system flush buffer_cache;
System altered.
22:00:55 SQL> select * from test6;
ABC
---------
1
2
22:02:59 SQL> set autotrace on explain
5.通过执行计划可以看到查询来自SYS_FBA_TCRV_75320，这是属于闪回归档表空间用于记录闪回数据。
22:03:14 SQL> select * from test6 as of scn 1372223;
ABC
---------
3
1
2
Execution Plan
----------------------------------------------------------
Plan hash value: 2569713660
--------------------------------------------------------------------------------
-------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-------------------------------
| 0 | SELECT STATEMENT | | 5 | 30 | 9 (
12)| 00:00:01 | | |
| 1 | VIEW | | 5 | 30 | 9 (
12)| 00:00:01 | | |
| 2 | UNION-ALL | | | |
| | | |
| 3 | PARTITION RANGE SINGLE| | 1 | 32 | 3
(0)| 00:00:01 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | SYS_FBA_HIST_75320 | 1 | 32 | 3
(0)| 00:00:01 | 1 | 1 |
|* 5 | FILTER | | | |
| | | |
|* 6 | HASH JOIN OUTER | | 4 | 8184 | 6 (
17)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | TEST6 | 4 | 72 | 2
(0)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | SYS_FBA_TCRV_75320 | 3 | 6084 | 3
(0)| 00:00:01 | | |
--------------------------------------------------------------------------------
-------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ENDSCN">1372223 AND "ENDSCN"<=1372730 AND ("STARTSCN" IS NULL OR
"STARTSCN"<=1372223))
5 - filter("STARTSCN"<=1372223 OR "STARTSCN" IS NULL)
6 - access("T".ROWID=CHARTOROWID("RID"(+)))
7 - filter("T"."VERSIONS_STARTSCN" IS NULL)
8 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>1372730) AND ("STARTSCN"(+) IS
NULL OR
"STARTSCN"(+)<1372730))
Note
-----
- dynamic sampling used for this statement (level=2)