oracle flashback archive,ORACLE 11G flashback archive

ORACLE 11G flashback archive

闪回数据存档，它是由一个或多个表空间存储对指定表进行的更改，也可对数据库级别做闪回数据存档

create flashback archive的内容要求：存档名，存档的第一个表空间，表空间的quota，存档多久

–创建保留1天的事务更改的存档trsen，创建不指定retention报错ORA-01009: missing mandatory parameter

SQL> create flashback archive trsen tablespace t_archive retention 1 day;

Flashback archive created.

SQL> create flashback archive trsen2 tablespace t_archive1 quota 200m ;

create flashback archive trsen2 tablespace t_archive1 quota 200m

*

ERROR at line 1:

ORA-01009: missing mandatory parameter

–创建存档trsen1，且限制quota为100m

SQL> create flashback archive trsen1 tablespace t_archive1 quota 100m retention 1 day;

Flashback archive created.

–为存档trsen1添加表空间，且限制quota为150m

SQL> alter flashback archive trsen add tablespace t_archive1 quota 150m;

Flashback archive altered.

–删除存档trsen1

SQL> drop flashback archive trsen1;

Flashback archive dropped

–修改存档trsen的retention

SQL> alter flashback archive trsen modify retention 1 month;

Flashback archive altered.

–设置trsen为默认存储

SQL> alter flashback archive trsen set default;

Flashback archive altered.

–某张被truncate的表从存档查出任何时间段里不同状态的数据

SQL> truncate table trsen_archive;

Table truncated.

SQL> SELECT * FROM TRSEN_ARCHIVE;

no rows selected

SQL> SELECT * FROM TRSEN_ARCHIVE AS OF TIMESTAMP TO_TIMESTAMP(‘2015-09-22 14:40:00′,’YYYY-MM-DD HH24:MI:SS’);

OBJECT_ID OBJECT_NAME

———- ——————————————————————————————————————————–

56242 USER_EXPFIL_INDEX_PARAMS

56252 USER_EXPFIL_XPATH_TAGS

56255 USER_EXPFIL_INDEXES

56256 USER_EXPFIL_INDEXES

56280 ALL_EXPFIL_PREDTAB_PLAN

56284 PARSEPARAMS

56305 DBMS_EXPFIL_DEPASEXP

56317 EXF$XPDATELST

56326 V32C

56369 SYS_MFBA_NROW

10 rows selected.

SQL> insert into trsen_archive select * from TRSEN_ARCHIVE AS OF TIMESTAMP TO_TIMESTAMP(‘2015-09-22 14:40:00′,’YYYY-MM-DD HH24:MI:SS’) where rownum< =5;

5 rows created.

SQL> commit;

Commit complete.

SQL> select * from trsen_archive;

OBJECT_ID OBJECT_NAME

———- ——————————————————————————————————————————–

56242 USER_EXPFIL_INDEX_PARAMS

56252 USER_EXPFIL_XPATH_TAGS

56255 USER_EXPFIL_INDEXES

56256 USER_EXPFIL_INDEXES

56280 ALL_EXPFIL_PREDTAB_PLAN

SQL> select * from TRSEN_ARCHIVE AS OF TIMESTAMP TO_TIMESTAMP(‘2015-09-22 14:40:00′,’YYYY-MM-DD HH24:MI:SS’);

OBJECT_ID OBJECT_NAME

———- ——————————————————————————————————————————–

56242 USER_EXPFIL_INDEX_PARAMS

56252 USER_EXPFIL_XPATH_TAGS

56255 USER_EXPFIL_INDEXES

56256 USER_EXPFIL_INDEXES

56280 ALL_EXPFIL_PREDTAB_PLAN

56284 PARSEPARAMS

56305 DBMS_EXPFIL_DEPASEXP

56317 EXF$XPDATELST

56326 V32C

56369 SYS_MFBA_NROW

10 rows selected.

SQL> delete trsen_archive where rownum< =4;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from TRSEN_ARCHIVE AS OF TIMESTAMP TO_TIMESTAMP(‘2015-09-22 14:40:00′,’YYYY-MM-DD HH24:MI:SS’);

OBJECT_ID OBJECT_NAME

———- ——————————————————————————————————————————–

56242 USER_EXPFIL_INDEX_PARAMS

56252 USER_EXPFIL_XPATH_TAGS

56255 USER_EXPFIL_INDEXES

56256 USER_EXPFIL_INDEXES

56280 ALL_EXPFIL_PREDTAB_PLAN

56284 PARSEPARAMS

56305 DBMS_EXPFIL_DEPASEXP

56317 EXF$XPDATELST

56326 V32C

56369 SYS_MFBA_NROW

10 rows selected.

SQL> select * from TRSEN_ARCHIVE AS OF TIMESTAMP TO_TIMESTAMP(‘2015-09-22 15:04:00′,’YYYY-MM-DD HH24:MI:SS’);

no rows selected

SQL> select * from TRSEN_ARCHIVE AS OF TIMESTAMP TO_TIMESTAMP(‘2015-09-22 15:06:00′,’YYYY-MM-DD HH24:MI:SS’);

OBJECT_ID OBJECT_NAME

———- ——————————————————————————————————————————–

56242 USER_EXPFIL_INDEX_PARAMS

56252 USER_EXPFIL_XPATH_TAGS

56255 USER_EXPFIL_INDEXES

56256 USER_EXPFIL_INDEXES

56280 ALL_EXPFIL_PREDTAB_PLAN

SQL> select * from trsen_archive;

OBJECT_ID OBJECT_NAME

———- ——————————————————————————————————————————–

56280 ALL_EXPFIL_PREDTAB_PLAN

针对做存档的表，不能做drop table动作

SQL> drop table trsen_archive1 purge;

drop table trsen_archive1 purge

*

ERROR at line 1:

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

SQL> alter table trsen_archive1 no flashback archive;

Table altered.

SQL> drop table trsen_archive1 purge;

Table dropped.