FBDA实验验证

2018年3月1日 4555点热度 0人点赞 0条评论
■建立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为合适的保留策略,并加以适当的维护,这个数据履历的功能就完美实现了。

liking

这个人很懒,什么都没留下

文章评论