[20240614]闪回查询的问题.txt

--//链接提到的查询问题,https://connor-mcdonald.com/2024/06/12/flashback-query-an-important-warning/
--//重复测试:

1.环境:
SCOTT@test01p> @ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.建立测试脚本测试:

$ cat aa.txt
drop table t purge;
create table t as select * from scott.emp;
alter table t add primary key ( empno );
begin
update t set sal=sal+1 where empno=7788; commit;
update t set sal=sal+1 where empno=7654; commit;
update t set sal=sal+1 where empno=7900; commit;
update t set sal=sal+1 where empno=7566; commit;
end;
/

$ cat ac.txt
drop table t purge;
create table t as select * from scott.emp;
alter table t add primary key ( empno );
host sleep 30
begin
update t set sal=sal+1 where empno=7788; commit;
update t set sal=sal+1 where empno=7654; commit;
update t set sal=sal+1 where empno=7900; commit;
update t set sal=sal+1 where empno=7566; commit;
end;
/


$ cat ab.txt
select
   decode( versions_operation
       , 'I', 'insert'
       , 'U', 'update'
       , 'D', 'delete'
            , 'original'
           ) op
  ,versions_xid
  ,versions_startscn
  ,versions_endscn
  ,rowid rid
from   t
versions between scn minvalue and maxvalue;

SCOTT@test01p> @ aa.txt
Table dropped.
Table created.
Table altered.
PL/SQL procedure successfully completed.

SCOTT@test01p> @ ab.txt
OP       VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN RID
-------- ---------------- ----------------- --------------- ------------------
original                                                    AAAHeMAALAAAAGrAAA
original                                                    AAAHeMAALAAAAGrAAB
original                                                    AAAHeMAALAAAAGrAAC
original                                                    AAAHeMAALAAAAGrAAD
original                                                    AAAHeMAALAAAAGrAAE
original                                                    AAAHeMAALAAAAGrAAF
original                                                    AAAHeMAALAAAAGrAAG
original                                                    AAAHeMAALAAAAGrAAH
original                                                    AAAHeMAALAAAAGrAAI
original                                                    AAAHeMAALAAAAGrAAJ
original                                                    AAAHeMAALAAAAGrAAK
original                                                    AAAHeMAALAAAAGrAAL
original                                                    AAAHeMAALAAAAGrAAM
original                                                    AAAHeMAALAAAAGrAAN
14 rows selected.
--//没有查询到相关update信息.

3.重复测试:
--//建立索引后加入sleep 30.
SCOTT@test01p> @ ac.txt
Table dropped.
Table created.
Table altered.
PL/SQL procedure successfully completed.

SCOTT@test01p> @ ab.txt
OP       VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN RID
-------- ---------------- ----------------- --------------- ------------------
update   06001900130C0000          17617376                 AAAHeOAALAAAAGrAAD
update   01001F00DE0B0000          17617374                 AAAHeOAALAAAAGrAAL
update   06001500140C0000          17617372                 AAAHeOAALAAAAGrAAE
update   08001B00FD0B0000          17617370                 AAAHeOAALAAAAGrAAH
original                                                    AAAHeOAALAAAAGrAAA
original                                                    AAAHeOAALAAAAGrAAB
original                                                    AAAHeOAALAAAAGrAAC
original                                           17617376 AAAHeOAALAAAAGrAAD
original                                           17617372 AAAHeOAALAAAAGrAAE
original                                                    AAAHeOAALAAAAGrAAF
original                                                    AAAHeOAALAAAAGrAAG
original                                           17617370 AAAHeOAALAAAAGrAAH
original                                                    AAAHeOAALAAAAGrAAI
original                                                    AAAHeOAALAAAAGrAAJ
original                                                    AAAHeOAALAAAAGrAAK
original                                           17617374 AAAHeOAALAAAAGrAAL
original                                                    AAAHeOAALAAAAGrAAM
original                                                    AAAHeOAALAAAAGrAAN
18 rows selected.
--//建立表 dml后sleep30秒,可以查询到相关信息.
--//作者的解析:

Important Note: Once you create a new table, some processing occurs in the background in the Oracle Database that will
subsequently allow for changes to be correctly tracked. If you run transactions immediately on a table after creating
it, some of those may be missed when it comes to later VERSIONS BETWEEN queries.

This is noted in the docs

Note: After executing a CREATE TABLE statement, wait at least 15 seconds to commit any transactions, to ensure that
Oracle Flashback features (especially Oracle Flashback Version Query) reflect those transactions.

--//当然这种情况在实际的生产环境很难遇到.