Re: Strange Flashback Query Behaviour

From: Sidney Chen <huanshengchen_at_gmail.com>
Date: Mon, 13 Feb 2012 15:16:13 +0800
Message-ID: <CAM_ddu9FFq+qE32YYkL81sWWBzdOsNuwGpbwGdsge7QO+YxTpA_at_mail.gmail.com>

hi Matt,
The rowscn is the scn when the block got change, not the commit scn. There is gap between the rowscn and the commit scn. Flashback query is based on the transaction commit scn, this is why you need to try to increment rowscn a bit to fetch the row.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns007.htm#SQLRF50953

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row in the current session. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.

On Mon, Feb 13, 2012 at 2:13 PM, Matt McClernon <mccmx_at_hotmail.com> wrote:

>
> 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
>
>
>

-- 
Regards
Sidney Chen


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

Received on Mon Feb 13 2012 - 01:16:13 CST