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)
********************************************************************************
