Flashback table tested with Reference tables(ie constraints)

***************** Flashback table ****************

Scenario 1:
A).

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE
SYS_TEMP_FBT TABLE

SQL> set time on
10:16:59 SQL>

10:16:59 SQL> select * from t;
NUM
----------
112
3
5

10:29:20 SQL> insert into t values(333);
1 row created.
10:29:37 SQL> insert into t values(4444);
1 row created.
10:29:45 SQL> commit;
Commit complete.

10:29:49 SQL> select * from t;
NUM
----------
112
3
5
333
4444

10:29:53 SQL> select * from t as of timestamp to_timestamp('16/07/2009:10:28:53','dd/mm/yyyy:hh:mi:ss');
NUM
----------
112
3
5

10:31:06 SQL> flashback table t to timestamp to_timestamp('16/07/2009:10:28:53','dd/mm/yyyy:hh:mi:ss');
Flashback complete.

10:31:33 SQL> select * from t;
NUM
----------
112
3
5

Scenario 2:


B).
*********** with constraint reference **************

create table b (no number,ano number constraint fk_ano references a(no));
alter table a add constraint pk_no primary key (no);

10:59:36 SQL> select * from user_constraints;
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION
------------------------------ ------------------------------ - ------------------------------ ---------------NV FK_ANO R B
NV PK_NO P A

SQL> select * from a;
NO
----------
0
33
3423
44444
123123
4.2322E+12
6 rows selected.

SQL> select * from b;
no rows selected

SQL> desc b
Name
-------------------------------------------------------------------------------------------------------------NO
ANO

SQL> insert into a values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into b values(1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> set time on
14:52:16 SQL>

14:52:17 SQL> select * from a as of timestamp to_timestamp('23/07/2009:02:48:00','dd/mm/yyyy:hh:mi:ss');
NO
----------
0
33
3423
44444
123123
4.2322E+12
6 rows selected.

14:53:01 SQL> select * from a;
NO
----------
0
1
33
3423
44444
123123
4.2322E+12
7 rows selected.

14:53:07 SQL> flashback table a to timestamp to_timestamp('23/07/2009:02:48:00','dd/mm/yyyy:hh:mi:ss');
flashback table a to timestamp to_timestamp('23/07/2009:02:48:00','dd/mm/yyyy:hh:mi:ss')
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (NV.FK_ANO) violated - child record found

14:53:48 SQL> alter table b enable row movement;
Table altered.

14:54:05 SQL> flashback table a,b to timestamp to_timestamp('23/07/2009:02:48:00','dd/mm/yyyy:hh:mi:ss');
Flashback complete.

14:54:09 SQL> select * from a;
NO
----------
0
33
3423
44444
123123
4.2322E+12
6 rows selected.

14:54:14 SQL> select * from b;
no rows selected

Scenario 3:
C).
******************************** DDL **************************

14:58:08 SQL> desc c
Name
---------------------------------------------------------------------------------------------------------
NO

14:58:10 SQL>
14:58:12 SQL>
14:58:21 SQL> alter table c add name varchar2(10);
Table altered.

14:58:59 SQL> insert into c values(4,'asdasdasda');
1 row created.
14:59:08 SQL> commit;
Commit complete.

14:59:10 SQL> select * from c;
NO NAME
---------- -----------------------------------------------------------------------------------------------
1
23
44
2
23333333
4 asdasdasda
6 rows selected.

14:59:33 SQL>
15:01:37 SQL> alter table c drop column name;
Table altered.

15:01:50 SQL> flashback table c to timestamp to_timestamp('23/07/2009:02:59:00','dd/mm/yyyy:hh:mi:ss');
flashback table c to timestamp to_timestamp('23/07/2009:02:59:00','dd/mm/yyyy:hh:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

15:02:41 SQL> alter table c enable row movement;
Table altered.

15:02:56 SQL> flashback table c to timestamp to_timestamp('23/07/2009:02:59:00','dd/mm/yyyy:hh:mi:ss');
flashback table c to timestamp to_timestamp('23/07/2009:02:59:00','dd/mm/yyyy:hh:mi:ss')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


******************** End *******************