配置与使用闪回之闪回数据归档

闪回数据归档

SQL> conn /as sysdba;

Connected.

SQL> desc dbms_flashback

SQL> select file_name from dba_data_files;

SQL> create tablespace flash_archive

  2  datafile '/home/gyh/oracle/oradata/orcl/arc.dbf'

  3  size 20m;

Tablespace created.

SQL> c/years/year

  1* create flashback archive fb_es tablespace flash_archive retention 1 year

SQL> /

Flashback archive created.

SQL> desc dba_flashback_archive

  1* select owner_name,flashback_archive_name from dba_flashback_archive

SQL> /

 

OWNER_NAME

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

FLASHBACK_ARCHIVE_NAME

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

SYS

FB_ES

SQL> create user fb identified by fb default tablespace flash_archive;

User created.

Table dropped.

SQL> grant connect,resource to fb;

Grant succeeded.

SQL> conn fb/fb

Connected.

SQL> create table test(id number);

Table created.

SQL> create table test1(id number);

Table created.

SQL> insert into test select rownum from dual connect by rownum<10000;

9999 rows created.

SQL> insert into test1 select rownum from dual connect by rownum<10000;

9999 rows created.

 

SQL> update test set id=1 where rownum<3;           

2 rows updated.

SQL> conn /as sysdba;

Connected.

SQL> grant flashback archive administer to fb;

Grant succeeded.

SQL> conn fb/fb;

Connected.

SQL> alter table test flashback archive fb_es;

Table altered.

SQL> select * from dba_flashback_archive_tables;

 

TABLE_NAME                     OWNER_NAME

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

FLASHBACK_ARCHIVE_NAME

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

ARCHIVE_TABLE_NAME                                    STATUS

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

TEST                           FB

FB_ES

SYS_FBA_HIST_77810                                    ENABLED

1* select * from test1 as of timestamp(systimestamp -interval '28' day)

SQL> /

select * from test1 as of timestamp(systimestamp -interval '28' day)

              *

ERROR at line 1:

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

SQL> c/1/'1'

  1* select count(*) from test as of timestamp(systimestamp-interval '1' year)

SQL> /

  COUNT(*)

----------

      9999

结论：在undo保留期内使用as of timestamp看不出是使用的是undo数据还是归档数据但是超出保留期的限制就有不同的提示结果显示了。