Re: flashback question

fitzjarrell_at_cox.net schrieb:
> Maxim Demenko wrote:
>
> Jonathan mentions setting event 10311 for 9.0 to alleviate this
> problem; the assumption this event would also help in 9.2 is false. I
> have tested this and the error remains the same. The only solution is
> to wait at least 5 minutes after the table is created before attempting
> to use flashback in 9.2. After the initial five minutes expire there
> is no further problem using flashback, provided, of course, the table
> is not dropped and recreated. At that point the five-minute clock on
> table 'age' starts again.
>
>
> David Fitzjarrell
>

That's interesting point, thank you , David , for detailed test. (btw., i didn't refer the thread due to setting of event, but only because Jonathan Lewis mentioned the errors as well if delay of 5 minutes will not be taken in account, nevertheless, you are correct, i personally implied 10311 will work in 9iR2).
However, there is a solution to avoid this delay in 9iR2 as well. Event didn't work by sql driven flashback, but it works as expected by using dbms_flashback.

oracle_at_col-fc1-02:~ >sqlplus scott/tiger

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Aug 9 10:48:18 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

SQL> create table t(a varchar2(1));

Table created.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

                   701707

SQL> insert into t values(1);

1 row created.

SQL> c/1/2

   1* insert into t values(2)
SQL> r

   1* insert into t values(2)

1 row created.

SQL> c/2/3

   1* insert into t values(3)
SQL> r

   1* insert into t values(3)

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

                   701730

SQL> delete t;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> alter session set events '10311 trace name context forever, level 1' ;

Session altered.

SQL> exec dbms_flashback.enable_at_system_change_number(701730)

PL/SQL procedure successfully completed.

SQL> select * from t;

A
-
1
2
3

Best regards

Maxim Received on Tue Aug 09 2005 - 04:00:52 CDT