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)

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