11GR2新特性测试-闪回归档

闪回数据归档测试
(1)版本
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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

(2)权限
flashback archive administer privilege

create user test identified by test default tablespace users;

grant connect,resource to test;

grant flashback archive administer to test;

(3)创建闪回归档
create flashback archive flash1
tablespace users
quota 10m
retention 1 year;

SQL> col owner_name for a10
SQL> col flashback_archive_name for a20

SQL> select owner_name,flashback_archive_name,FLASHBACK_ARCHIVE#,RETENTION_IN_DAYS,status from  dba_flashback_archive;

OWNER_NAME FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# RETENTION_IN_DAYS STATUS
---------- -------------------- ------------------ ----------------- -------
TEST       FLASH1                                1               365

SQL> col tablespace_name for a10       
SQL> select flashback_archive_name,tablespace_name,quota_in_mb from dba_flashback_archive_ts

FLASHBACK_ARCHIVE_NA TABLESPACE QUOTA_IN_MB
-------------------- ---------- ----------------------------------------
FLASH1               USERS      10

(4)创建闪回归档表
create table t(name varchar2(30),address varchar2(50))
flashback archive flash1;

SQL> col table_name for a10
SQL> col archive_table_name for a20
SQL> select * from dba_flashback_archive_tables

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME   STATUS
---------- ---------- -------------------- -------------------- --------
T          TEST       FLASH1               SYS_FBA_HIST_74484   ENABLED

(5)查询闪回记录
SQL> select * from test.SYS_FBA_HIST_74484;
select * from test.SYS_FBA_HIST_74484
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into t values('a','b');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t values('a2','b');
1 row created.
SQL> insert into t values('a2','b2');
1 row created.
SQL> commit;
Commit complete.
SQL> delete from t where name='a';
1 row deleted.
SQL> commit;
Commit complete.
SQL> update t set address='c' where name='a';
0 rows updated.
SQL> update t set address='c' where name='a2';
2 rows updated.
SQL> commit;
Commit complete.
SQL>  select * from test.SYS_FBA_HIST_74484;
no rows selected

SQL> desc  test.SYS_FBA_HIST_74484;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RID                                                VARCHAR2(4000)
 STARTSCN                                           NUMBER
 ENDSCN                                             NUMBER
 XID                                                RAW(8)
 OPERATION                                          VARCHAR2(1)
 NAME                                               VARCHAR2(30)
 ADDRESS                                            VARCHAR2(50)


SQL> select * from t as of timestamp(systimestamp-interval '10' minute);
NAME                  ADDRESS
--------------------------------------------------
a                     b

SQL> select * from t;
NAME                  ADDRESS
--------------------------------------------------
a2                    c
c

SQL> set linesize 100
SQL> col rid for a20
SQL> col name for a10
SQL> col address for a10
SQL> select * from test.SYS_FBA_HIST_74484

RID                    STARTSCN     ENDSCN XID              O NAME       ADDRESS
-------------------- ---------- ---------- ---------------- - ---------- ----------
AAASL0AAEAAAAMQAAB      1046635    1046839 0B0002000F000000 I a2         b
AAASL0AAEAAAAMQAAC      1046635    1046839 0B0002000F000000 I a2         b2
AAASL0AAEAAAAMQAAA      1046516    1046640 110020000E000000 I a          b

问题:
只有插入记录,没有更新,删除记录?

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-773507/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-773507/