[20120813]11GR2下flashback data archive的测试.txt

        oracle 11GR2有一个新特性,就是flashback data archive,就是通过一个表空间记录表的一些变化,查询历史数据.自己知道这个特性,从来
也没有测试过.实际上这个就是flashback table的扩展(个人认为)!.

测试如下:

1.测试环境:

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

--建立表空间,并且flashback archive保留1年.

create tablespace fratable datafile '/u01/app/oracle11g/oradata/test/fratable01.dbf' size 100M;

create flashback archive fra_1year tablespace fratable retention 1 year;

2.建立测试表

create table nofra (id number, ins_date date, ins_scn number);

create table   fra   (id number, ins_date date, ins_scn number);

insert into  nofra select 1,sysdate,current_scn from v$database ;

insert into    fra select 101,sysdate,current_scn from v$database ;

commit;

SQL> select * from nofra ;

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

         1 2012-08-13 11:22:19   16006875

SQL> select * from fra ;

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

       101 2012-08-13 11:22:19   16006889

3.开始测试:

alter table fra flashback archive fra_1year;

SQL> column x new_value x format a30

SQL> select sysdate x from dual;

X

------------------------------

2012-08-13 11:28:45

--删除了nofra表记录

delete from nofra;

commit;

SQL> select * from nofra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss');

old   1: select * from nofra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss')

new   1: select * from nofra as of timestamp to_timestamp('2012-08-13 11:28:45','yyyy-mm-dd:hh24:mi:ss')

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

         1 2012-08-13 11:22:19   16006875

--删除了fra表记录

delete from fra;

commit;

SQL> select * from fra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss');

old   1: select * from fra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss')

new   1: select * from fra as of timestamp to_timestamp('2012-08-13 11:28:45','yyyy-mm-dd:hh24:mi:ss')

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

       101 2012-08-13 11:22:19   16006889

--这个受undo的影响,我这里是测试环境,估计要等上一阵子,下午再测!
--解决方法是切换undo,删除旧undo,或者建立一个小的undo并且不扩展.做一些大事务就覆盖原来的undo了.

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

4.看看执行计划如何!

SQL>  select * from nofra as of timestamp to_timestamp('2012-08-13 11:28:45','yyyy-mm-dd:hh24:mi:ss');

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

         1 2012-08-13 11:22:19   16006875

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  d93pfbuxp0n5c, child number 7

-------------------------------------

 select * from nofra as of timestamp to_timestamp('2012-08-13

11:28:45','yyyy-mm-dd:hh24:mi:ss')

Plan hash value: 1697631366

---------------------------------------------------------

| Id  | Operation         | Name  | E-Rows | Cost (%CPU)|

---------------------------------------------------------

|   0 | SELECT STATEMENT  |       |        |     2 (100)|

|   1 |  TABLE ACCESS FULL| NOFRA |     82 |     2   (0)|

---------------------------------------------------------

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

20 rows selected.

SQL>  select * from fra as of timestamp to_timestamp('2012-08-13 11:28:45','yyyy-mm-dd:hh24:mi:ss');

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

       101 2012-08-13 11:22:19   16006889

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  dqqz4d9kzjmxj, child number 5

-------------------------------------

 select * from fra as of timestamp to_timestamp('2012-08-13

11:28:45','yyyy-mm-dd:hh24:mi:ss')

Plan hash value: 3129699971

----------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name                | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |                     |        |    13 (100)|       |       |          |

|   1 |  VIEW                     |                     |     21 |    13   (8)|       |       |          |

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

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

|   4 |     PARTITION RANGE SINGLE|                     |      1 |     3   (0)|       |       |          |

|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_104229 |      1 |     3   (0)|       |       |          |

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

|*  7 |     HASH JOIN OUTER       |                     |     20 |    10  (10)|   823K|   823K|  189K (0)|

|*  8 |      TABLE ACCESS FULL    | FRA                 |     20 |     6   (0)|       |       |          |

|*  9 |      TABLE ACCESS FULL    | SYS_FBA_TCRV_104229 |      1 |     3   (0)|       |       |          |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("TIMESTAMP_TO_SCN"(TIMESTAMP' 2012-08-13 11:28:45.000000000')

   5 - filter(("ENDSCN""TIMESTAMP_TO_SCN"(TIMESTAMP' 2012-08-13

              11:28:45.000000000') AND ("STARTSCN" IS NULL OR "STARTSCN"

              2012-08-13 11:28:45.000000000'))))

   6 - filter(("STARTSCN"

              "STARTSCN" IS NULL))

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

   8 - filter("T"."VERSIONS_STARTSCN" IS NULL)

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

              "STARTSCN"

Note

-----

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

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

43 rows selected.

select table_name from dba_tables  where tablespace_name='FRATABLE';

TABLE_NAME

------------------------------

SYS_FBA_DDL_COLMAP_104229

SYS_FBA_TCRV_104229

以及一个分区表 SYS_FBA_HIST_104229.

5.做一些DDL操作看看.

SQL> insert into    fra select 102,sysdate,current_scn from v$database ;

1 row created.

SQL> insert into   nofra select 2,sysdate,current_scn from v$database ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from nofra ;

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

         2 2012-08-13 15:34:26   16406037

SQL> select * from fra ;

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

       102 2012-08-13 15:34:18   16406033

alter table nofra add (pad  varchar2(32));

alter table   fra add (pad  varchar2(32));

SQL> select * from nofra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss');

old   1: select * from nofra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss')

new   1: select * from nofra as of timestamp to_timestamp('2012-08-13 11:28:45','yyyy-mm-dd:hh24:mi:ss')

        ID INS_DATE               INS_SCN PAD

---------- ------------------- ---------- --------------------------------

         1 2012-08-13 11:22:19   16006875

SQL> select * from fra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss');

old   1: select * from fra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss')

new   1: select * from fra as of timestamp to_timestamp('2012-08-13 11:28:45','yyyy-mm-dd:hh24:mi:ss')

        ID INS_DATE               INS_SCN PAD

---------- ------------------- ---------- --------------------------------

       101 2012-08-13 11:22:19   16006889

--查询历史数据正常!        不过多了1个PAD字段(原来没有).

--删除字段看看!

SQL> alter table nofra drop column pad;

Table altered.

SQL> alter table fra drop column pad;

Table altered.

SQL> select * from nofra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss');

old   1: select * from nofra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss')

new   1: select * from nofra as of timestamp to_timestamp('2012-08-13 11:28:45','yyyy-mm-dd:hh24:mi:ss')

select * from nofra as of timestamp to_timestamp('2012-08-13 11:28:45','yyyy-mm-dd:hh24:mi:ss')

              *

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

SQL> select * from fra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss');

old   1: select * from fra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss')

new   1: select * from fra as of timestamp to_timestamp('2012-08-13 11:28:45','yyyy-mm-dd:hh24:mi:ss')

        ID INS_DATE               INS_SCN PAD

---------- ------------------- ---------- --------------------------------

       101 2012-08-13 11:22:19   16006889

--表nofra删除了字段,出现ORA-01466: unable to read data - table definition has changed.
--而表fra字段我做了add再delete.查询这个时间点,应该还没有pad字段.看来oracle还是存在一些问题.

SQL> column type format a30

SQL> column HISTORICAL_COLUMN_NAME format a30

SQL> column column_name format a30

SQL> select * from SYS_FBA_DDL_COLMAP_104229;

  STARTSCN     ENDSCN XID              O COLUMN_NAME                    TYPE                           HISTORICAL_COLUMN_NAME

---------- ---------- ---------------- - ------------------------------ ------------------------------ ------------------------------

  16406145   16406275                    D_16406275_PAD                 VARCHAR2(32)                   PAD

  16006837                               ID                             NUMBER                         ID

  16006837                               INS_DATE                       DATE                           INS_DATE

  16006837                               INS_SCN                        NUMBER                         INS_SCN

--oracle有点问题,startscn=16406145仅仅是插入字段pad的scn,ENDSCN=16406275是删除字段pad.记录的仅仅是scn,而不包括对应的时间点.
--如果查询当时的scn应该没有pad字段.

SQL> select * from fra as of scn  16006889;
        ID INS_DATE               INS_SCN
---------- ------------------- ----------
       101 2012-08-13 11:22:19   16006889

--查询scn再建表以前或者时间呢时间很久呢?不知道这种情况算bug吗?不知道scn小到一定程度又可以查询.

SQL> select * from fra as of scn   880;

        ID INS_DATE               INS_SCN PAD

---------- ------------------- ---------- --------------------------------

       101 2012-08-13 11:22:19   16006889

SQL> select * from fra as of timestamp to_timestamp('2012-08-01 11:22:19','yyyy-mm-dd:hh24:mi:ss');

        ID INS_DATE               INS_SCN PAD

---------- ------------------- ---------- --------------------------------

       101 2012-08-13 11:22:19   16006889

--查询scn在SYS_FBA_DDL_COLMAP_104229记录的scn.

SQL> select * from fra as of scn   16006837;

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

       101 2012-08-13 11:22:19   16006889

SQL> select * from fra as of scn   16006836;

select * from fra as of scn   16006836

              *

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

6.做一个truncate看看.

SQL> column y new_value y format a30

SQL> select sysdate y,current_scn from v$database ;

Y                              CURRENT_SCN

------------------------------ -----------

2012-08-13 16:09:29               16407378

SQL> truncate table nofra;

Table truncated.

SQL> truncate table fra;

Table truncated.

--注:truncate 表fra有点慢!

SQL> select * from nofra as of timestamp to_timestamp('&y','yyyy-mm-dd:hh24:mi:ss');

old   1: select * from nofra as of timestamp to_timestamp('&y','yyyy-mm-dd:hh24:mi:ss')

new   1: select * from nofra as of timestamp to_timestamp('2012-08-13 16:09:29','yyyy-mm-dd:hh24:mi:ss')

select * from nofra as of timestamp to_timestamp('2012-08-13 16:09:29','yyyy-mm-dd:hh24:mi:ss')

              *

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

SQL> select * from fra as of timestamp to_timestamp('&y','yyyy-mm-dd:hh24:mi:ss');

old   1: select * from fra as of timestamp to_timestamp('&y','yyyy-mm-dd:hh24:mi:ss')

new   1: select * from fra as of timestamp to_timestamp('2012-08-13 16:09:29','yyyy-mm-dd:hh24:mi:ss')

        ID INS_DATE               INS_SCN PAD

---------- ------------------- ---------- --------------------------------

       102 2012-08-13 15:34:18   16406033

SQL> select * from fra as of  scn  16407378;

        ID INS_DATE               INS_SCN

---------- ------------------- ----------

       102 2012-08-13 15:34:18   16406033

SQL> select * from fra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss');

old   1: select * from fra as of timestamp to_timestamp('&x','yyyy-mm-dd:hh24:mi:ss')

new   1: select * from fra as of timestamp to_timestamp('2012-08-13 11:28:45','yyyy-mm-dd:hh24:mi:ss')

        ID INS_DATE               INS_SCN PAD

---------- ------------------- ---------- --------------------------------

       101 2012-08-13 11:22:19   16006889

--时间点X,Y都可以查询.不过truncate fra表确实很慢.

SQL> select * from fra;

no rows selected

SQL> select table_name from dba_tables  where tablespace_name='FRATABLE';

TABLE_NAME

------------------------------

SYS_FBA_DDL_COLMAP_104229

SYS_FBA_TCRV_104229

SQL> select object_name,object_id,data_object_id from dba_objects  where object_name='FRA';

OBJECT_NAME    OBJECT_ID DATA_OBJECT_ID

------------- ---------- --------------

FRA               104229         104244

7.最后一点,具有flashback data archive的表是不能drop的.要drop先取消flashback data archive特性.

SQL> drop table fra ;

drop table fra

           *

ERROR at line 1:

ORA-55610: Invalid DDL statement on history-tracked table

SQL> drop table nofra ;

Table dropped.

--取消flashback data archive特性.

SQL> alter table fra no flashback archive ;

Table altered.

SQL> drop table fra ;

Table dropped.

8.总结:
11GR2 又前进了1步,我的记忆里面11GR1版本是不支持删除列以及truncate的操作的.