Re: Strange Flashback Query Behaviour

Don't forget that 11.2 gives you deferred segment creation by default, and this may complicate the issue. Try the test with segment creation immediate; and if you get the same error try creating the table, then waiting about 3 minutes before inserting. There used to be some interesting granulairty issues with SCNs and DDL.

Regards
Jonathan Lewis

mccmx_at_hotmail.com wrote:
>
> In our 11.2.0.3 EE database the first row insert right after a CREATE TAB=
> LE does not have a VERSIONS_STARTSCN or VERSIONS_XID associated with it, =
> and we cannot query the data as it existed between the CREATE and the INS=
> ERT. Does anyone else experience this behaviour (test case below).
> It's as if Oracle does not differentiate between the SCN of the CREATE an=
> d the first INSERT. Subsequent INSERTS behave as expected.
>
> DROP TABLE BadSCN;CREATE TABLE BadSCN(test_field NUMBER);INSERT INTO BadS=
> CN VALUES(1);commit;SELECT versions_startscn, versions_endscn, versions_x=
> id, test_field FROM BadSCNVERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
> Table dropped.
> SQL>Table created.
> SQL>1 row created.
> SQL>Commit complete.
> SQL> 2
> VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID TEST_FIELD--------=
> --------- --------------- ---------------- ---------- =
> =
> 1
> 1* select ora_rowscn, test_field FROM BadSCNSQL> /
> ORA_ROWSCN TEST_FIELD---------- ---------- 1079019 =
> 1
>
> SQL> select * from BadSCN as of scn 1079019;select * from BadSCN as of sc=
> n 1079019 *ERROR at line 1:ORA-01466: unable t=
> o read data - table definition has changed
>
> SQL> select * from BadSCN as of scn 1079020;select * from BadSCN as of sc=
> n 1079020 *ERROR at line 1:ORA-01466: unable t=
> o read data - table definition has changed
>
> SQL> select * from BadSCN as of scn 1079021;
> TEST_FIELD---------- 1
> --

--
http://www.freelists.org/webpage/oracle-l