[bbk5106] 第40集 - 第四章 Flashback Database 04

Oracle Total Recall Scenario

Using Flashback Data Archive to access historical data:

--create the flashback data archive

create flashback archive default fla1 tablespace tbs1 quota 10g retention 5 year;

--specifiy the default flashback data archive 

alter flashback archive fla1 set default;

--enable flashback data archive

alter table inventory flashback archive;

alter table stock_data flashback archive;

select product_number,product_name,count from inventory as of timestamp to_timestamp('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss');

Optionally,adding space:

alter flashback archive fla1 add tablespace tbs3 quota 5g;

optionally,changing retention time:

alter flashback archive fla1 modify retention 2 year;

optionally,purging data:

alter flashback archive fla1 purge before timestamp(systimestamp - interval '1` day);

optionally,dropping a flashback data archive:

drop flashback archive fla1;

Viewing Flashback Data Archives

Viewing the results:

View Name(DBA/USER)	Description
*_FLASHBACK_ARCHIVE	Displays information about Flashback Data Archives
*_FLASHBACK_ARCHIVE_TS	Displays tablespaces of Flashbackd Data Archives
*_FLASHBACK_ARCHIVE_TABLES	Displays information about tables that are enabled for flashback archiving

 

 

 

 

 

查询哪张表纳入了数据闪回归档区?

SQL> select table_name,owner_name,status from user_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     STATUS
------------------------------ ------------------------------ --------
NETSTORE_INCOMEEXPENDITURELIST ARCER                          ENABLED
EMP                            ARCER                          ENABLED

其他与纳入数据闪回归档区的相关的表空间信息、状态信息等可参照上述表格中提供的数据字典进行查询.