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