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

[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. --//当然这种情况在实际的生产环境很难遇到.