Re: flashback question

From: <fitzjarrell_at_cox.net>
Date: 8 Aug 2005 23:51:57 -0700
Message-ID: <1123570317.036302.134440@g43g2000cwa.googlegroups.com>

Maxim Demenko wrote:
> Eugene schrieb:
> > Hi all,
> > I have a question about flashback. For some reason it doesn't work?!
> > Here is what I do:
> >
> > show parameter undo_retention;
> > NAME TYPE VALUE
> > ------------------------------------ ----------- -----
> > undo_retention integer 5400
> >
> > create table t (varchar2(1));
> > Table created.
> >
> > select dbms_flashback.get_system_change_number from dual;
> > GET_SYSTEM_CHANGE_NUMBER
> > ------------------------
> > 2927483661
> >
> > insert into t values (1);
> > 1 row created.
> >
> > insert into t values (2);
> > 1 row created.
> >
> > select dbms_flashback.get_system_change_number from dual;
> > GET_SYSTEM_CHANGE_NUMBER
> > ------------------------
> > 2927483700
> >
> > insert into t values(3);
> > 1 row created.
> >
> > select dbms_flashback.get_system_change_number from dual;
> > GET_SYSTEM_CHANGE_NUMBER
> > ------------------------
> > 2927483714
> >
> > commit;
> > Commit complete.
> >
> > select * from t;
> > A
> > -
> > 1
> > 2
> > 3
> >
> > delete from t;
> > 3 rows deleted.
> >
> > commit;
> > Commit complete.
> >
> > select * from t;
> > no rows selected
> >
> > select * from t as of scn 2927483700;
> > select * from t as of scn 2927483700
> > *
> > ERROR at line 1:
> > ORA-01466: unable to read data - table definition has changed
> >
> > ----------------------------------------------
> > Why didn't it work??? Did I do something wrong?
> > ----------------------------------------------
> >
> > Thanks,
> > Eugene
> >

>

> It depends on Oracle version you are working with, in 9iR2 for example
> you should wait about 5 min after table was created to avoid this error.
> You may find interesting the comment of Jonathan Lewis in thread
> http://groups.google.de/group/comp.databases.oracle.server/browse_frm/thread/9a32d6aad92cc174/35c4d8b40047baa8?q=event+10311&rnum=1&hl=de#35c4d8b40047baa8

>

> Best regards

>
> Maxim

Waiting 5 minutes after the table is created:

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

Table created.

SQL>
SQL> exec dbms_lock.sleep(300);

PL/SQL procedure successfully completed.

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

---

                44829269

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

---

            44829273

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

---

         44829275

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 44829273

A

-

1

2

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 44829275

A

-

1

2

3

SQL> 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 Received on Tue Aug 09 2005 - 01:51:57 CDT