Re: flashback question

From: <fitzjarrell_at_cox.net>
Date: 9 Aug 2005 05:56:43 -0700
Message-ID: <1123592203.125201.49380@g44g2000cwa.googlegroups.com>

Maxim Demenko wrote:
> 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

An interesting point, indeed, proving again 9.2.0.6 has issues with some functionality using the SQL*Plus engine but apparently works properly (with the event set) from PL/SQL. And, apparently this is fixed in 10g; possibly 9.2.0.7 will emerge and correct this problem.

Thank you for properly continuing the test I started; using dbms_flashback had not occurred to be after event 10311 was set as both methods, prior to the five minute limit, failed to produce proper results and I presumed, quite incorrectly, the same behaviour would ensue.

This is the test as it should have been run, showing how Oracle does and does not behave according to the event:

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

Table created.

SQL>
SQL> -- exec dbms_lock.sleep(300);
SQL> alter session set events '10311 trace name context forever, level

1';

Session altered.

SQL>
SQL> column get_system_change_number new_value d1
SQL>
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

---

                44836903

SQL>
SQL> insert into t values (1);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> insert into t values (2);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> column system_change_number new_value d2
SQL> select dbms_flashback.get_system_change_number

system_change_number from dual;

SYSTEM_CHANGE_NUMBER

---

            44836907

SQL>
SQL> insert into t values(3);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> column get_change_number new_value d3
SQL> select dbms_flashback.get_system_change_number get_change_number

from dual;

GET_CHANGE_NUMBER

---

         44836909

SQL>
SQL> select * from t;

A
-
1
2
3

SQL>
SQL> delete from t;

3 rows deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from t;

no rows selected

SQL>
SQL> select * from t as of scn &d2;
old 1: select * from t as of scn &d2
new 1: select * from t as of scn 44836907 select * from t as of scn 44836907

              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL>
SQL> exec dbms_flashback.enable_at_system_change_number(&d2);

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

A
-
1
2

SQL>
SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t as of scn &d3;
old 1: select * from t as of scn &d3
new 1: select * from t as of scn 44836909 select * from t as of scn 44836909

              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL>
SQL> exec dbms_flashback.enable_at_system_change_number(&d3);

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

A
-
1
2
3

SQL>
SQL> exec dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL> David Fitzjarrell Received on Tue Aug 09 2005 - 07:56:43 CDT