今天flashback table的时候出现了ORA-08194错误，临时办法将错误操作前的数据保存到另外一张表上面。

下面是自己测试的笔记。

SQL> create table tt (id int,name varchar2(20));

Table created.

[@more@]

SQL> insert into tt values(1,'bruce');

1 row created.

SQL> commit;

Commit complete.

SQL> create materialized view tt_mv as select * from tt;

Materialized view created.

SQL> select dbms_flashback.get_system_change_number() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
34274375

SQL> insert into tt values(2,'bruce1');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tt;

ID NAME
---------- ----------------------------------------
1 bruce
2 bruce1

SQL> select * from tt_mv;

ID NAME
---------- ----------------------------------------
1 bruce

SQL> flashback table tt to scn 34274375;
flashback table tt to scn 34274375
*
ERROR at line 1:
ORA-08194: 在实体化视图上不允许闪回表操作

由于user的错误操作，建立了mv的table上不能flashback，

此时为了保护正确的数据，

需要将create table tt_new保存误操作前的数据

SQL> create table tt_new as select * from tt as of scn 34274375;

Table created.

SQL> select * from tt_new
2 ;

ID NAME
---------- ----------------------------------------
1 bruce

ITPub博客

flashback table 出现 ORA-08194: 在实体化视图上不允许闪回表操作

最新文章