Strange Flashback Query Behaviour
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----------------- --------------- ---------------- ---------- 11* 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