Strange Flashback Query Behaviour

From: Matt McClernon <mccmx_at_hotmail.com>
Date: Mon, 13 Feb 2012 06:13:27 +0000
Message-ID: <BAY171-W726534D7D434F9F8CF1750B77F0_at_phx.gbl>


In our 11.2.0.3 EE database the first row insert right after a CREATE TABLE 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 INSERT.  Does anyone else experience this behaviour (test case below). It's as if Oracle does not differentiate between the SCN of the CREATE and the first INSERT.  Subsequent INSERTS behave as expected.

DROP TABLE BadSCN;CREATE TABLE BadSCN(test_field NUMBER);INSERT INTO BadSCN VALUES(1);commit;SELECT versions_startscn, versions_endscn, versions_xid,  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 scn 1079019              *ERROR at line 1:ORA-01466: unable to read data - table definition has changed

SQL> select * from BadSCN as of scn 1079020;select * from BadSCN as of scn 1079020              *ERROR at line 1:ORA-01466: unable to read data - table definition has changed

SQL> select * from BadSCN as of scn 1079021; TEST_FIELD----------         1
                                               --
http://www.freelists.org/webpage/oracle-l Received on Mon Feb 13 2012 - 00:13:27 CST

Original text of this message