[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的操作的.
时间： 2024-10-23 00:21:45