Oracle中flashback的使用

1、闪回技术都包含哪些技术

• flashback query
• flashback data archive
• flashback transaction query
• flashback transaction
• flashback table
• flashback drop
• flashback database

2、flashback query【主要是利用undo查询一段时间以前的数据】

• 闪回时间点查询

select * from test as of timestamp to_timestamp('','') where object_id=100;

 

• 闪回版本查询

3、flashback data archive【将原本只能保存在undo中的撤销数据保存在普通表空间中，这样可以查询表很长时间以前的旧数据】

•  如何对表创建flashback data archive。

SQL> create flashback archive default fda_default tablespace fda retention 1 year;          

Flashback archive created.


SQL> grant flashback archive on fda_default to dayu;

Grant succeeded.

SQL> alter table dayu.fda_test flashback archive;

Table altered.

SQL> select count(*) from dayu.fda_test as of timestamp (systimestamp -interval '7' month);
select count(*) from dayu.fda_test as of timestamp (systimestamp -interval '7' month)
                          *
ERROR at line 1:
ORA-08180: no snapshot found based on specified time


SQL> select count(*) from dayu.fda_test as of timestamp (systimestamp -interval '1' day);
select count(*) from dayu.fda_test as of timestamp (systimestamp -interval '1' day)
                          *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

 

• 在fda中，创建的flashback data archive是如何存储的？

SEGMENT_NAME                                      SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
FDA_TEST                                      TABLE
SYS_FBA_DDL_COLMAP_73240                              TABLE
SYS_FBA_TCRV_73240                                  TABLE
SYS_FBA_HIST_73240                                  TABLE PARTITION                               --这个表存放的就是fda_test的历史数据

SYS_FBA_TCRV_IDX_73240 INDEX

 

• 开起来flashback data archive的表如果有不被允许的ddl会怎么样？

SQL> alter table dayu.fda_test shrink space;
alter table dayu.fda_test shrink space
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

• 哪些ddl可以在开启历史表的的表上操作？

1、对字段的add、drop、rename、modify修改
2、对约束的add、drop、rename修改
3、对分区的drop、truncate操作
4、对表的truncate和rename操作

 

4、flashback transaction query【需要开启最小补充日志；查询返回的结果是将当前数据修改为以前数据的撤销sql】

 

5、flashback transaction【撤销已经执行的事务】

•  查询过去一段时间表上的事务

select distinct xid,commit_scn
from flashback_transaction_query
where table_name='TEST'
and table_owner='DAYU'
and commit_timestamp > systimestamp - interval '10' minute
order by commit_scn;           --没有结果呢怎么？

 

6、flashback table【利用undo将表恢复到以前的状态】

1.  模拟测试

SQL> update scn_test set object_name='DY' where rownum < 20000;

19999 rows updated.

SQL> commit;

Commit complete.

SQL> flashback table scn_test to timestamp (systimestamp -interval '10' miniute);
flashback table scn_test to timestamp (systimestamp -interval '10' miniute)
                                                                   *
ERROR at line 1:
ORA-30089: missing or invalid <datetime field>


SQL> flashback table scn_test to timestamp (systimestamp -interval '10' minute);
flashback table scn_test to timestamp (systimestamp -interval '10' minute)
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


SQL> alter table scn_test enable row movement;

Table altered.

SQL> flashback table scn_test to timestamp (systimestamp -interval '10' minute);

Flashback complete.

SQL> select object_name from scn_test where rownum< 10;

OBJECT_NAME
------------------------------
V$RSRC_CONS_GROUP_HISTORY
V_$RSRC_PLAN_HISTORY
V$RSRC_PLAN_HISTORY
V_$BLOCKING_QUIESCE
V$BLOCKING_QUIESCE
V_$PX_BUFFER_ADVICE
V_$FLASHBACK_DATABASE_LOG
V$FLASHBACK_DATABASE_LOG
V_$FLASHBACK_DATABASE_STAT

9 rows selected.

• 开启flashback table的几个条件

1. 对表开启row movement
2. undo要设置的合适
3. 另外sys用户的表是无法做flashback的。

 

7、flashback drop【从回收站中将表恢复】

 

8、flashback database【需要开启闪回日志以及undo】

转载于:https://www.cnblogs.com/dayu-liu/p/11046370.html