flashback table与主外键参照表

由于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