ITPub博客

首页 > Linux操作系统 > Linux操作系统 > flashback table与主外键参照表

flashback table与主外键参照表

原创 Linux操作系统 作者:cqubityj 时间:2007-12-26 12:09:12 0 删除 编辑

由于flashback table时oracle要确保表中记录符合主外健约束,因此在某些情况下可能无法单独flashback主表,这时需要将主表和子表放在一起flashback.

SQL> create table p (x number primary key);

Table created

SQL> create table c (y number references p(x));

Table created.

SQL> insert into p values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into c values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 8057214182
 
SQL> insert into p values (2);

1 row created.

SQL> insert into c values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table p enable row movement;

Table altered.

SQL> alter table c enable row movement;

Table altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 8057214258

SQL> select * from p;

         X
----------
         1
         2

SQL> select * from c;

         Y
----------
         1
         2

SQL> flashback table p to scn 8057214182;
flashback table p to scn 8057214182
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (USER1.SYS_C0014384) violated - child record
found
单独flashback主表报错.需要将主表与子表同时flashback.

SQL> flashback table p,c to scn 8057214182;

Flashback complete.

SQL> select * from p;

         X
----------
         1

SQL> select * from c;

         Y
----------
         1

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/228190/viewspace-1448/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-19

  • 博文量
    133
  • 访问量
    423471