Strange Flashback Query Behaviour
- From: Matt McClernon <mccmx@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 13 Feb 2012 06:13:27 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
