flashback table导致rowid变化

首先看测试 SQL>? create table test_move as select * from dba_users; Table created. SQL> create user zhoul identified by zhoul; User created. SQL> grant dba to zhoul; Grant succeeded. SQL> conn zhoul/zhoul Connected. SQL>? create table test_move as select * from dba_users;? Table created. SQL> select count(*) from test_move; ? COUNT(*) ---------- ??????? 28 SQL> select username,rowid from test_move; USERNAME?????????????????????? ROWID ------------------------------ ------------------ SYSTEM???????????????????????? AAAOkdAAEAAAAR8AAA SYS??????????????????????????? AAAOkdAAEAAAAR8AAB TEST?????????????????????????? AAAOkdAAEAAAAR8AAC OEM??????????????????????????? AAAOkdAAEAAAAR8AAD ZHOUL????????????????????????? AAAOkdAAEAAAAR8AAE ZZ???????????????????????????? AAAOkdAAEAAAAR8AAF SCOTT????????????????????????? AAAOkdAAEAAAAR8AAG STRADMIN?????????????????????? AAAOkdAAEAAAAR8AAH ASSET????????????????????????? AAAOkdAAEAAAAR8AAI MGMT_VIEW????????????????????? AAAOkdAAEAAAAR8AAJ OUTLN????????????????????????? AAAOkdAAEAAAAR8AAK USERNAME?????????????????????? ROWID ------------------------------ ------------------ DBSNMP???????????????????????? AAAOkdAAEAAAAR8AAL OLAPSYS??????????????????????? AAAOkdAAEAAAAR8AAM SI_INFORMTN_SCHEMA???????????? AAAOkdAAEAAAAR8AAN ORDPLUGINS???????????????????? AAAOkdAAEAAAAR8AAO XDB??????????????????????????? AAAOkdAAEAAAAR8AAP ANONYMOUS????????????????????? AAAOkdAAEAAAAR8AAQ CTXSYS???????????????????????? AAAOkdAAEAAAAR8AAR WMSYS????????????????????????? AAAOkdAAEAAAAR8AAS DMSYS????????????????????????? AAAOkdAAEAAAAR8AAT EXFSYS???????????????????????? AAAOkdAAEAAAAR8AAU ORDSYS???????????????????????? AAAOkdAAEAAAAR8AAV USERNAME?????????????????????? ROWID ------------------------------ ------------------ MDSYS????????????????????????? AAAOkdAAEAAAAR8AAW DIP??????????????????????????? AAAOkdAAEAAAAR8AAX MDDATA???????????????????????? AAAOkdAAEAAAAR8AAY TSMSYS???????????????????????? AAAOkdAAEAAAAR8AAZ ORACLE_OCM???????????????????? AAAOkdAAEAAAAR8AAa SYSMAN???????????????????????? AAAOkdAAEAAAAR8AAb 28 rows selected. SQL> select current_scn from v$database; CURRENT_SCN ----------- 1.1000E+13 SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 10999711206848 SQL> delete from test_move where username='ZHOUL'; 1 row deleted. SQL> commit; Commit complete. SQL> select username,rowid from test_move; USERNAME?????????????????????? ROWID ------------------------------ ------------------ SYSTEM???????????????????????? AAAOkdAAEAAAAR8AAA SYS??????????????????????????? AAAOkdAAEAAAAR8AAB TEST?????????????????????????? AAAOkdAAEAAAAR8AAC OEM??????????????????????????? AAAOkdAAEAAAAR8AAD ZZ???????????????????????????? AAAOkdAAEAAAAR8AAF SCOTT????????????????????????? AAAOkdAAEAAAAR8AAG STRADMIN?????????????????????? AAAOkdAAEAAAAR8AAH ASSET????????????????????????? AAAOkdAAEAAAAR8AAI MGMT_VIEW????????????????????? AAAOkdAAEAAAAR8AAJ OUTLN????????????????????????? AAAOkdAAEAAAAR8AAK DBSNMP???????????????????????? AAAOkdAAEAAAAR8AAL USERNAME?????????????????????? ROWID ------------------------------ ------------------ OLAPSYS??????????????????????? AAAOkdAAEAAAAR8AAM SI_INFORMTN_SCHEMA???????????? AAAOkdAAEAAAAR8AAN ORDPLUGINS???????????????????? AAAOkdAAEAAAAR8AAO XDB??????????????????????????? AAAOkdAAEAAAAR8AAP ANONYMOUS????????????????????? AAAOkdAAEAAAAR8AAQ CTXSYS???????????????????????? AAAOkdAAEAAAAR8AAR WMSYS????????????????????????? AAAOkdAAEAAAAR8AAS DMSYS????????????????????????? AAAOkdAAEAAAAR8AAT EXFSYS???????????????????????? AAAOkdAAEAAAAR8AAU ORDSYS???????????????????????? AAAOkdAAEAAAAR8AAV MDSYS????????????????????????? AAAOkdAAEAAAAR8AAW USERNAME?????????????????????? ROWID ------------------------------ ------------------ DIP??????????????????????????? AAAOkdAAEAAAAR8AAX MDDATA???????????????????????? AAAOkdAAEAAAAR8AAY TSMSYS???????????????????????? AAAOkdAAEAAAAR8AAZ ORACLE_OCM???????????????????? AAAOkdAAEAAAAR8AAa SYSMAN???????????????????????? AAAOkdAAEAAAAR8AAb 27 rows selected. SQL>? flashback table test_move to scn 10999711206848; flashback table test_move to scn 10999711206848 ???????????????? * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled SQL> alter table test_move enable row movement; Table altered. SQL> alter session set sql_trace=true; Session altered. SQL> flashback table test_move to scn 10999711206848; Flashback complete. SQL> select username,rowid from test_move; USERNAME?????????????????????? ROWID ------------------------------ ------------------ SYSTEM???????????????????????? AAAOkdAAEAAAAR8AAE SYS??????????????????????????? AAAOkdAAEAAAAR8AAc TEST?????????????????????????? AAAOkdAAEAAAAR8AAd OEM??????????????????????????? AAAOkdAAEAAAAR8AAe ZHOUL????????????????????????? AAAOkdAAEAAAAR8AAf ZZ???????????????????????????? AAAOkdAAEAAAAR8AAg SCOTT????????????????????????? AAAOkdAAEAAAAR8AAh STRADMIN?????????????????????? AAAOkdAAEAAAAR8AAi ASSET????????????????????????? AAAOkdAAEAAAAR8AAj MGMT_VIEW????????????????????? AAAOkdAAEAAAAR8AAk OUTLN????????????????????????? AAAOkdAAEAAAAR8AAl USERNAME?????????????????????? ROWID ------------------------------ ------------------ DBSNMP???????????????????????? AAAOkdAAEAAAAR8AAm OLAPSYS??????????????????????? AAAOkdAAEAAAAR8AAn SI_INFORMTN_SCHEMA???????????? AAAOkdAAEAAAAR8AAo ORDPLUGINS???????????????????? AAAOkdAAEAAAAR8AAp XDB??????????????????????????? AAAOkdAAEAAAAR8AAq ANONYMOUS????????????????????? AAAOkdAAEAAAAR8AAr CTXSYS???????????????????????? AAAOkdAAEAAAAR8AAs WMSYS????????????????????????? AAAOkdAAEAAAAR8AAt DMSYS????????????????????????? AAAOkdAAEAAAAR8AAu EXFSYS???????????????????????? AAAOkdAAEAAAAR8AAv ORDSYS???????????????????????? AAAOkdAAEAAAAR8AAw USERNAME?????????????????????? ROWID ------------------------------ ------------------ MDSYS????????????????????????? AAAOkdAAEAAAAR8AAx DIP??????????????????????????? AAAOkdAAEAAAAR8AAy MDDATA???????????????????????? AAAOkdAAEAAAAR8AAz TSMSYS???????????????????????? AAAOkdAAEAAAAR8AA0 ORACLE_OCM???????????????????? AAAOkdAAEAAAAR8AA1 SYSMAN???????????????????????? AAAOkdAAEAAAAR8AA2 28 rows selected. SQL> alter session set sql_trace=false; Session altered. 可以看到rowid已经发生变化，进一步查看后台跟踪文件，发现flashback table其实是做了delete和insert操作。 ******************************************************************************** DELETE /*+ BYPASS_UJVC */ FROM (SELECT /*+ ORDERED USE_NL(S) PARALLEL(S,? DEFAULT) PARALLEL(T,DEFAULT) */ S.rowid FROM SYS_TEMP_FBT T,? "ZHOUL"."TEST_MOVE" S WHERE T.rid = S.rowid and T.action = 'D' and T.object#? = : 1) V call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows ------- ------? -------- ---------- ---------- ---------- ----------? ---------- Parse??????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0 Execute????? 1????? 0.00?????? 0.00????????? 0????????? 5???????? 34????????? 28 Fetch??????? 0????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0 ------- ------? -------- ---------- ---------- ---------- ----------? ---------- total??????? 2????? 0.00?????? 0.00????????? 0????????? 5???????? 34????????? 28 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 67???? (recursive depth: 1) Rows???? Row Source Operation -------? --------------------------------------------------- ????? 0? DELETE? TEST_MOVE (cr=5 pr=0 pw=0 time=9221 us) ???? 28?? PX COORDINATOR? (cr=5 pr=0 pw=0 time=8462 us) ????? 0??? PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us) ????? 0???? NESTED LOOPS? (cr=0 pr=0 pw=0 time=0 us) ????? 0????? PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us) ????? 0?????? TABLE ACCESS FULL SYS_TEMP_FBT (cr=0 pr=0 pw=0 time=0 us) ????? 0????? TABLE ACCESS BY USER ROWID TEST_MOVE (cr=0 pr=0 pw=0 time=0 us) ******************************************************************************** INSERT /*+ PARALLEL(S,DEFAULT) PARALLEL(T,DEFAULT) */ INTO ? "ZHOUL"."TEST_MOVE" SELECT /*+ USE_NL(S) ORDERED PARALLEL(S,DEFAULT) ? PARALLEL(T,DEFAULT) */ S.* FROM SYS_TEMP_FBT T,"ZHOUL"."TEST_MOVE" as of ? SCN :1 S WHERE T.rid = S.rowid and T.action = 'I' and T.object# = :2 call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows ------- ------? -------- ---------- ---------- ---------- ----------? ---------- Parse??????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0 Execute????? 1????? 0.00?????? 1.03????????? 0????????? 5????????? 5????????? 28 Fetch??????? 0????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0 ------- ------? -------- ---------- ---------- ---------- ----------? ---------- total??????? 2????? 0.00?????? 1.03????????? 0????????? 5????????? 5????????? 28 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 67???? (recursive depth: 1) Rows???? Row Source Operation -------? --------------------------------------------------- ???? 28? PX COORDINATOR? (cr=3 pr=0 pw=0 time=9972 us) ????? 0?? PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us) ????? 0??? NESTED LOOPS? (cr=0 pr=0 pw=0 time=0 us) ????? 0???? PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us) ????? 0????? TABLE ACCESS FULL SYS_TEMP_FBT (cr=0 pr=0 pw=0 time=0 us) ????? 0???? TABLE ACCESS BY USER ROWID TEST_MOVE (cr=0 pr=0 pw=0 time=0 us) ********************************************************************************