■建立FBDA所在的表空间likingfbda CREATE TABLESPACE likingfbda DATAFILE SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 1G; ■在表空间上建立FBDA，retention为1个月 CREATE FLASHBACK ARCHIVE likingfbda TABLESPACE likingfbda RETENTION 1 MONTH; ■设置默认FBDA ALTER FLASHBACK ARCHIVE likingfbda SET DEFAULT; col flashback_archive_name for a30 SELECT flashback_archive_name,status FROM dba_flashback_archive; FLASHBACK_ARCHIVE_NAME STATUS ------------------------------ -------- LIKINGFBDA DEFAULT ■建立一个小的undo tablespace用于测试 SYS@prod1> show parameter undo NAME TYPE VALUE ------------------------ ----------- ------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 CREATE UNDO TABLESPACE likingundotbs DATAFILE SIZE 4m; ALTER SYSTEM SET UNDO_TABLESPACE=likingundotbs; SYS@prod1> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string LIKINGUNDOTBS ■授权用户scott可以使用flashback archive GRANT FLASHBACK ARCHIVE ON likingfbda TO scott; ■设置table启用flashback archive SYS@prod1> conn scott/tiger SCOTT@prod1> CREATE TABLE emp1 AS SELECT * FROM emp; SCOTT@prod1> ALTER TABLE emp1 FLASHBACK ARCHIVE;
以下继续：
■建立测试环境（查询闪回归档可以基于scn或timestamp）
SYS@prod1> select current_scn from v$database;
CURRENT_SCN
---------------
1602437
SCOTT@prod1> select count(*) from emp1;
COUNT(*)
---------------
14
SCOTT@prod1> delete from emp1 where EMPNO<7788;
7 rows deleted.
SCOTT@prod1> commit;
循环脚本，覆盖undo block：
SCOTT@prod1> create table emp2 as select * from emp;
begin
for i in 1..1000 loop
insert into emp2 select * from emp;
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SCOTT@prod1> set autotrace on
SCOTT@prod1> select count(*) from emp1 as of scn 1602437;
COUNT(*)
---------------
14
Execution Plan
----------------------------------------------------------
Plan hash value: 3317502069
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 23 (9)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | VIEW | | 8 | | 23 (9)| 00:00:01 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | PARTITION RANGE SINGLE| | 7 | 196 | 14 (0)| 00:00:01 | 1 | 1 |
|* 5 | TABLE ACCESS FULL | SYS_FBA_HIST_88967 | 7 | 196 | 14 (0)| 00:00:01 | 1 | 1 |
|* 6 | FILTER | | | | | | | |
| 7 | MERGE JOIN OUTER | | 1 | 2040 | 9 (23)| 00:00:01 | | |
| 8 | SORT JOIN | | 1 | 12 | 6 (17)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | EMP1 | 1 | 12 | 5 (0)| 00:00:01 | | |
|* 10 | SORT JOIN | | 1 | 2028 | 3 (34)| 00:00:01 | | |
|* 11 | TABLE ACCESS FULL | SYS_FBA_TCRV_88967 | 1 | 2028 | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("ENDSCN">1602437 AND "ENDSCN"<=1605534 AND ("STARTSCN" IS NULL OR "STARTSCN"<=1602437)
AND ("OPERATION" IS NULL OR "OPERATION"<>'D'))
6 - filter("STARTSCN"<=1602437 OR "STARTSCN" IS NULL)
9 - filter(("T"."VERSIONS_STARTSCN" IS NULL OR "T"."VERSIONS_STARTSCN"<=1602437) AND
("T"."VERSIONS_ENDSCN" IS NULL OR "T"."VERSIONS_ENDSCN">1602437) AND ("T"."VERSIONS_OPERATION" IS NULL
OR "T"."VERSIONS_OPERATION"<>'D'))
10 - access("RID"(+)=ROWIDTOCHAR("T".ROWID))
filter("RID"(+)=ROWIDTOCHAR("T".ROWID))
11 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>1605534) AND ("STARTSCN"(+) IS NULL OR
"STARTSCN"(+)<1605534))
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
76 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
通过查询的执行计划可以看到，数据是从SYS_FBA_TCRV_88967（闪回区）读出，可以证明此历史数据不是从undo block而是flashback archive读出。
继续：
■最值得验证的时刻来了，多次修改员工7788的工资数，看看历史变化情况！
SELECT versions_xid, versions_startscn, versions_endscn, empno, sal
FROM emp1 versions BETWEEN TIMESTAMP(systimestamp - INTERVAL '15' minute) AND maxvalue
WHERE empno = 7788
ORDER BY 2 NULLS FIRST;
UPDATE emp1 SET sal = 40000 WHERE empno = 7788;
commit;
UPDATE emp1 SET sal = 50000 WHERE empno = 7788;
commit;
UPDATE emp1 SET sal = 60000 WHERE empno = 7788;
commit;
UPDATE emp1 SET sal = 45000 WHERE empno = 7788;
commit;
VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN EMPNO SAL
---------------- ----------------- --------------- --------------- ---------------
1606144 7788 3000
1900110006000000 1606144 1606150 7788 4000
1B00060007000000 1606150 1606153 7788 5000
18001C0006000000 1606153 1606156 7788 6000
1700170006000000 1606156 7788 4500
SCOTT@prod1> select scn_to_timestamp(1606150) scn from dual;
SCN
--------------------------------------
01-MAR-18 04.41.04.000000000 PM
SELECT to_char(scn_to_timestamp(1606150), 'yyyy-mm-dd hh24:mi:ss') chr,
timestamp_to_scn(scn_to_timestamp(1606150)) dt
FROM dual;
CHR DT
------------------- ---------------
2018-03-01 16:41:04 1606148
select timestamp_to_scn(sysdate-1) scn from dual;
将scn显示为时间
col start_time for a35
col end_time for a35
SELECT to_char(scn_to_timestamp(nvl(versions_startscn, timestamp_to_scn(SYSDATE - 1))), 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(scn_to_timestamp(nvl(versions_endscn, timestamp_to_scn(SYSDATE))), 'yyyy-mm-dd hh24:mi:ss') end_time,
empno,
sal
FROM emp1 versions BETWEEN TIMESTAMP(systimestamp - INTERVAL '5' DAY) AND maxvalue
WHERE empno = 7788
ORDER BY 2,1 NULLS FIRST;
START_TIME END_TIME EMPNO SAL
------------------- ------------------- ---- -----
2016-02-03 10:28:54 2018-03-01 16:40:52 7788 3000
2018-03-01 16:40:52 2018-03-01 16:41:04 7788 4000
2018-03-01 16:41:04 2018-03-01 16:41:04 7788 5000
2018-03-01 16:41:04 2018-03-01 16:41:04 7788 6000
2018-03-01 16:41:04 2018-03-01 17:31:20 7788 4500
2018-03-01 17:31:20 2018-03-01 17:31:35 7788 40000
2018-03-01 17:31:35 2018-03-01 17:31:41 7788 50000
2018-03-01 17:31:41 2018-03-01 17:31:53 7788 60000
2018-03-01 17:31:53 2018-03-01 17:32:08 7788 45000
以上可以清晰的看到某个时间段员工7788的工资数是多少！
所以如果设置关键表的FBDA为合适的保留策略，并加以适当的维护，这个数据履历的功能就完美实现了。
文章评论