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)

********************************************************************************