Row movement的实验
在闪回表时，需要alter table_name enable row movement;为什么要使用row movement?因为本质上flashback table是进行了一系列DML操作，先是删除，然后再把数据插入。这样的变化中，原来数据的ROWID本身已经被删除，而附近的ROWID没有变化，要想还原数据，必须给还原的数据分配一个新的ROWID，而且需要把数据放到原先在表中的位置（比如，原来是在表的第3个位置，现在还原还是第3个位置），这样就会引起ROWID的重新排序，所以要启动row movement。
本实验想通过删除表中第一行数据，中间一行数据，最后一行数据来看看ROWID发生了什么变化。
SQL> select empno,ename,rowid from emp1;
EMPNO ENAME ROWID
---------- ---------- ------------------
3 abc AAANNGAAEAAAAIUAAB
7369 SMITH AAANNGAAEAAAAIUAAC
7499 ALLEN AAANNGAAEAAAAIUAAD
7521 WARD AAANNGAAEAAAAIUAAE
7566 JONES AAANNGAAEAAAAIUAAF
7654 MARTIN AAANNGAAEAAAAIUAAG
7698 BLAKE AAANNGAAEAAAAIUAAH
7782 CLARK AAANNGAAEAAAAIUAAI
7788 SCOTT AAANNGAAEAAAAIUAAJ
7839 KING AAANNGAAEAAAAIUAAK
7844 TURNER AAANNGAAEAAAAIUAAL
7876 ADAMS AAANNGAAEAAAAIUAAM
7900 JAMES AAANNGAAEAAAAIUAAN
7902 FORD AAANNGAAEAAAAIUAAO
7934 MILLER AAANNGAAEAAAAIUAAP
22 abcd AAANNGAAEAAAAIUAAQ
0 abc AAANNGAAEAAAAIUAAS
已选择17行。
第一步，删除第一行数据：
SQL> delete from emp1 where empno=3;
已删除 1 行。
SQL> select empno,ename,rowid from emp1;
EMPNO ENAME ROWID
---------- ---------- ------------------
7369 SMITH AAANNGAAEAAAAIUAAC
7499 ALLEN AAANNGAAEAAAAIUAAD
7521 WARD AAANNGAAEAAAAIUAAE
7566 JONES AAANNGAAEAAAAIUAAF
7654 MARTIN AAANNGAAEAAAAIUAAG
7698 BLAKE AAANNGAAEAAAAIUAAH
7782 CLARK AAANNGAAEAAAAIUAAI
7788 SCOTT AAANNGAAEAAAAIUAAJ
7839 KING AAANNGAAEAAAAIUAAK
7844 TURNER AAANNGAAEAAAAIUAAL
7876 ADAMS AAANNGAAEAAAAIUAAM
7900 JAMES AAANNGAAEAAAAIUAAN
7902 FORD AAANNGAAEAAAAIUAAO
7934 MILLER AAANNGAAEAAAAIUAAP
22 abcd AAANNGAAEAAAAIUAAQ
0 abc AAANNGAAEAAAAIUAAS
已选择16行。
SQL> alter table emp1 enable row movement;
表已更改。
SQL> flashback table emp1 to timestamp(systimestamp-interval'3' minute);
闪回完成。
SQL> select empno,ename,rowid from emp1;
EMPNO ENAME ROWID
---------- ---------- ------------------
3 abc AAANNGAAEAAAAIUAAA
7369 SMITH AAANNGAAEAAAAIUAAB
7499 ALLEN AAANNGAAEAAAAIUAAR
7521 WARD AAANNGAAEAAAAIUAAT
7566 JONES AAANNGAAEAAAAIUAAU
7654 MARTIN AAANNGAAEAAAAIUAAV
7698 BLAKE AAANNGAAEAAAAIUAAW
7782 CLARK AAANNGAAEAAAAIUAAX
7788 SCOTT AAANNGAAEAAAAIUAAY
7839 KING AAANNGAAEAAAAIUAAZ
7844 TURNER AAANNGAAEAAAAIUAAa
7876 ADAMS AAANNGAAEAAAAIUAAb
7900 JAMES AAANNGAAEAAAAIUAAc
7902 FORD AAANNGAAEAAAAIUAAd
7934 MILLER AAANNGAAEAAAAIUAAe
22 abcd AAANNGAAEAAAAIUAAf
0 abc AAANNGAAEAAAAIUAAg
已选择17行。
发现删除后其他行的数据，包括ROWID没有发生什么变化。但在还原后（row movement），所有行的ROWID都发生了变化。
第二步：删除中间一行数据：
SQL> delete from emp1 where empno=7499;
已删除 1 行。
SQL> select empno,ename,rowid from emp1;
EMPNO ENAME ROWID
---------- ---------- ------------------
3 abc AAANNGAAEAAAAIUAAA
7369 SMITH AAANNGAAEAAAAIUAAB
7521 WARD AAANNGAAEAAAAIUAAT
7566 JONES AAANNGAAEAAAAIUAAU
7654 MARTIN AAANNGAAEAAAAIUAAV
7698 BLAKE AAANNGAAEAAAAIUAAW
7782 CLARK AAANNGAAEAAAAIUAAX
7788 SCOTT AAANNGAAEAAAAIUAAY
7839 KING AAANNGAAEAAAAIUAAZ
7844 TURNER AAANNGAAEAAAAIUAAa
7876 ADAMS AAANNGAAEAAAAIUAAb
7900 JAMES AAANNGAAEAAAAIUAAc
7902 FORD AAANNGAAEAAAAIUAAd
7934 MILLER AAANNGAAEAAAAIUAAe
22 abcd AAANNGAAEAAAAIUAAf
0 abc AAANNGAAEAAAAIUAAg
已选择16行。
SQL> alter table emp1 enable row movement;
表已更改。
SQL> flashback table emp1 to timestamp(systimestamp-interval'3' minute);
闪回完成。
SQL> select empno,ename,rowid from emp1;
EMPNO ENAME ROWID
---------- ---------- ------------------
3 abc AAANNGAAEAAAAIUAAC
7369 SMITH AAANNGAAEAAAAIUAAD
7499 ALLEN AAANNGAAEAAAAIUAAE
7521 WARD AAANNGAAEAAAAIUAAF
7566 JONES AAANNGAAEAAAAIUAAG
7654 MARTIN AAANNGAAEAAAAIUAAH
7698 BLAKE AAANNGAAEAAAAIUAAI
7782 CLARK AAANNGAAEAAAAIUAAJ
7788 SCOTT AAANNGAAEAAAAIUAAK
7839 KING AAANNGAAEAAAAIUAAL
7844 TURNER AAANNGAAEAAAAIUAAM
7876 ADAMS AAANNGAAEAAAAIUAAN
7900 JAMES AAANNGAAEAAAAIUAAO
7902 FORD AAANNGAAEAAAAIUAAP
7934 MILLER AAANNGAAEAAAAIUAAQ
22 abcd AAANNGAAEAAAAIUAAR
0 abc AAANNGAAEAAAAIUAAS
已选择17行。
发现删除后其他行的数据，包括ROWID没有发生什么变化。但在还原后（row movement），所有行的ROWID都发生了变化。
第三步：删除最后一行数据
SQL> delete from emp1 where empno=0;
已删除 1 行。
SQL> alter table emp1 enable row movement;
表已更改。
SQL> select empno,ename,rowid from emp1;
EMPNO ENAME ROWID
---------- ---------- ------------------
3 abc AAANNGAAEAAAAIUAAC
7369 SMITH AAANNGAAEAAAAIUAAD
7499 ALLEN AAANNGAAEAAAAIUAAE
7521 WARD AAANNGAAEAAAAIUAAF
7566 JONES AAANNGAAEAAAAIUAAG
7654 MARTIN AAANNGAAEAAAAIUAAH
7698 BLAKE AAANNGAAEAAAAIUAAI
7782 CLARK AAANNGAAEAAAAIUAAJ
7788 SCOTT AAANNGAAEAAAAIUAAK
7839 KING AAANNGAAEAAAAIUAAL
7844 TURNER AAANNGAAEAAAAIUAAM
7876 ADAMS AAANNGAAEAAAAIUAAN
7900 JAMES AAANNGAAEAAAAIUAAO
7902 FORD AAANNGAAEAAAAIUAAP
7934 MILLER AAANNGAAEAAAAIUAAQ
22 abcd AAANNGAAEAAAAIUAAR
已选择16行。
SQL> flashback table emp1 to timestamp(systimestamp-interval'3' minute);
闪回完成。
SQL> select empno,ename,rowid from emp1;
EMPNO ENAME ROWID
---------- ---------- ------------------
3 abc AAANNGAAEAAAAIUAAA
7369 SMITH AAANNGAAEAAAAIUAAB
7499 ALLEN AAANNGAAEAAAAIUAAS
7521 WARD AAANNGAAEAAAAIUAAT
7566 JONES AAANNGAAEAAAAIUAAU
7654 MARTIN AAANNGAAEAAAAIUAAV
7698 BLAKE AAANNGAAEAAAAIUAAW
7782 CLARK AAANNGAAEAAAAIUAAX
7788 SCOTT AAANNGAAEAAAAIUAAY
7839 KING AAANNGAAEAAAAIUAAZ
7844 TURNER AAANNGAAEAAAAIUAAa
7876 ADAMS AAANNGAAEAAAAIUAAb
7900 JAMES AAANNGAAEAAAAIUAAc
7902 FORD AAANNGAAEAAAAIUAAd
7934 MILLER AAANNGAAEAAAAIUAAe
22 abcd AAANNGAAEAAAAIUAAf
0 abc AAANNGAAEAAAAIUAAg
已选择17行。
同样，发现删除后其他行的数据，包括ROWID没有发生什么变化。但在还原后（row movement），所有行的ROWID都发生了变化。这就证明删除位置与ROWID的移动没有必然关系，不是说是第一行删除后面的ROWID就跟着递增移动，最后一行删除前面的ROWID就可以保持不变。但也发现，此表中ROWID的变化只发生在ROWID的最后一位的字母上，而且变化总体上是字母递增顺序的，同时也可以看出一些行的ROWID经过几次变化后又变成了原来的ROWID.
|
未做删除前数据 |
第一次删除（第一行数据）还原后 |
第二次删除（中间行数据）还原后 |
第三次删除（最后一行数据）还原后 |
|
EMPNO ENAME ROWID ---------- ---------- ------------------ 3 abc AAANNGAAEAAAAIUAAB 7369 SMITH AAANNGAAEAAAAIUAAC 7499 ALLEN AAANNGAAEAAAAIUAAD 7521 WARD AAANNGAAEAAAAIUAAE 7566 JONES AAANNGAAEAAAAIUAAF 7654 MARTIN AAANNGAAEAAAAIUAAG 7698 BLAKE AAANNGAAEAAAAIUAAH 7782 CLARK AAANNGAAEAAAAIUAAI 7788 SCOTT AAANNGAAEAAAAIUAAJ 7839 KING AAANNGAAEAAAAIUAAK 7844 TURNER AAANNGAAEAAAAIUAAL 7876 ADAMS AAANNGAAEAAAAIUAAM 7900 JAMES AAANNGAAEAAAAIUAAN 7902 FORD AAANNGAAEAAAAIUAAO 7934 MILLER AAANNGAAEAAAAIUAAP 22 abcd AAANNGAAEAAAAIUAAQ 0 abc AAANNGAAEAAAAIUAAS
|
EMPNO ENAME ROWID ---------- ---------- ------------------ 3 abc AAANNGAAEAAAAIUAAA 7369 SMITH AAANNGAAEAAAAIUAAB 7499 ALLEN AAANNGAAEAAAAIUAAR 7521 WARD AAANNGAAEAAAAIUAAT 7566 JONES AAANNGAAEAAAAIUAAU 7654 MARTIN AAANNGAAEAAAAIUAAV 7698 BLAKE AAANNGAAEAAAAIUAAW 7782 CLARK AAANNGAAEAAAAIUAAX 7788 SCOTT AAANNGAAEAAAAIUAAY 7839 KING AAANNGAAEAAAAIUAAZ 7844 TURNER AAANNGAAEAAAAIUAAa 7876 ADAMS AAANNGAAEAAAAIUAAb 7900 JAMES AAANNGAAEAAAAIUAAc 7902 FORD AAANNGAAEAAAAIUAAd 7934 MILLER AAANNGAAEAAAAIUAAe 22 abcd AAANNGAAEAAAAIUAAf 0 abc AAANNGAAEAAAAIUAAg
|
EMPNO ENAME ROWID ---------- ---------- ------------------ 3 abc AAANNGAAEAAAAIUAAC 7369 SMITH AAANNGAAEAAAAIUAAD 7499 ALLEN AAANNGAAEAAAAIUAAE 7521 WARD AAANNGAAEAAAAIUAAF 7566 JONES AAANNGAAEAAAAIUAAG 7654 MARTIN AAANNGAAEAAAAIUAAH 7698 BLAKE AAANNGAAEAAAAIUAAI 7782 CLARK AAANNGAAEAAAAIUAAJ 7788 SCOTT AAANNGAAEAAAAIUAAK 7839 KING AAANNGAAEAAAAIUAAL 7844 TURNER AAANNGAAEAAAAIUAAM 7876 ADAMS AAANNGAAEAAAAIUAAN 7900 JAMES AAANNGAAEAAAAIUAAO 7902 FORD AAANNGAAEAAAAIUAAP 7934 MILLER AAANNGAAEAAAAIUAAQ 22 abcd AAANNGAAEAAAAIUAAR 0 abc AAANNGAAEAAAAIUAAS
|
EMPNO ENAME ROWID ---------- ---------- ------------------ 3 abc AAANNGAAEAAAAIUAAA 7369 SMITH AAANNGAAEAAAAIUAAB 7499 ALLEN AAANNGAAEAAAAIUAAS 7521 WARD AAANNGAAEAAAAIUAAT 7566 JONES AAANNGAAEAAAAIUAAU 7654 MARTIN AAANNGAAEAAAAIUAAV 7698 BLAKE AAANNGAAEAAAAIUAAW 7782 CLARK AAANNGAAEAAAAIUAAX 7788 SCOTT AAANNGAAEAAAAIUAAY 7839 KING AAANNGAAEAAAAIUAAZ 7844 TURNER AAANNGAAEAAAAIUAAa 7876 ADAMS AAANNGAAEAAAAIUAAb 7900 JAMES AAANNGAAEAAAAIUAAc 7902 FORD AAANNGAAEAAAAIUAAd 7934 MILLER AAANNGAAEAAAAIUAAe 22 abcd AAANNGAAEAAAAIUAAf 0 abc AAANNGAAEAAAAIUAAg
|
后又对中间数据empno=7839进行了删除，对中间数据empno=7876进行了删除。发现ROWID的变化是这样的：第一次删除操作和第三次删除操作每行数据的ROWID大体相等，也就是说第三次的删除操作后ROWID会变回到第一次的，只是其中个别ROWID会发生改变。而第五次的也同第一次，第三次的ROWID相似，变化的也是个别的。但相邻的删除操作ROWID的变化却很大，几乎发现不了相邻两次DML操作ROWID不变的数据。这也说明删除操作与数据的位置有影响：第一行的删除或最后一行的删除ROWID变化很小这里就发现只有一行的ROWID发生改变，而中间行的移动ROWID变化比较大。可看下表比较：
|
第一次删除（第一行数据）还原后 |
第二次删除（中间行数据）还原后 |
第三次删除（最后一行数据）还原后 |
第四次删除（中间行数据）还原后 |
第五次删除（中间行数据）还原后 |
|
EMPNO ENAME ROWID ---------- ---------- ---------------- 3 abc AAANNGAAEAAAAIUAAA 7369 SMITH AAANNGAAEAAAAIUAAB 7499 ALLEN AAANNGAAEAAAAIUAAR 7521 WARD AAANNGAAEAAAAIUAAT 7566 JONES AAANNGAAEAAAAIUAAU 7654 MARTIN AAANNGAAEAAAAIUAAV 7698 BLAKE AAANNGAAEAAAAIUAAW 7782 CLARK AAANNGAAEAAAAIUAAX 7788 SCOTT AAANNGAAEAAAAIUAAY 7839 KING AAANNGAAEAAAAIUAAZ 7844 TURNER AAANNGAAEAAAAIUAAa 7876 ADAMS AAANNGAAEAAAAIUAAb 7900 JAMES AAANNGAAEAAAAIUAAc 7902 FORD AAANNGAAEAAAAIUAAd 7934 MILLER AAANNGAAEAAAAIUAAe 22 abcd AAANNGAAEAAAAIUAAf 0 abc AAANNGAAEAAAAIUAAg
|
EMPNO ENAME ROWID ---------- ---------- ---------------- 3 abc AAANNGAAEAAAAIUAAC 7369 SMITH AAANNGAAEAAAAIUAAD 7499 ALLEN AAANNGAAEAAAAIUAAE 7521 WARD AAANNGAAEAAAAIUAAF 7566 JONES AAANNGAAEAAAAIUAAG 7654 MARTIN AAANNGAAEAAAAIUAAH 7698 BLAKE AAANNGAAEAAAAIUAAI 7782 CLARK AAANNGAAEAAAAIUAAJ 7788 SCOTT AAANNGAAEAAAAIUAAK 7839 KING AAANNGAAEAAAAIUAAL 7844 TURNER AAANNGAAEAAAAIUAAM 7876 ADAMS AAANNGAAEAAAAIUAAN 7900 JAMES AAANNGAAEAAAAIUAAO 7902 FORD AAANNGAAEAAAAIUAAP 7934 MILLER AAANNGAAEAAAAIUAAQ 22 abcd AAANNGAAEAAAAIUAAR 0 abc AAANNGAAEAAAAIUAAS
|
EMPNO ENAME ROWID ---------- ---------- ---------------- 3 abc AAANNGAAEAAAAIUAAA 7369 SMITH AAANNGAAEAAAAIUAAB 7499 ALLEN AAANNGAAEAAAAIUAAS 7521 WARD AAANNGAAEAAAAIUAAT 7566 JONES AAANNGAAEAAAAIUAAU 7654 MARTIN AAANNGAAEAAAAIUAAV 7698 BLAKE AAANNGAAEAAAAIUAAW 7782 CLARK AAANNGAAEAAAAIUAAX 7788 SCOTT AAANNGAAEAAAAIUAAY 7839 KING AAANNGAAEAAAAIUAAZ 7844 TURNER AAANNGAAEAAAAIUAAa 7876 ADAMS AAANNGAAEAAAAIUAAb 7900 JAMES AAANNGAAEAAAAIUAAc 7902 FORD AAANNGAAEAAAAIUAAd 7934 MILLER AAANNGAAEAAAAIUAAe 22 abcd AAANNGAAEAAAAIUAAf 0 abc AAANNGAAEAAAAIUAAg
|
EMPNO ENAME ROWID ---------- ---------- ---------------- 3 abc AAANNGAAEAAAAIUAAC 7369 SMITH AAANNGAAEAAAAIUAAD 7499 ALLEN AAANNGAAEAAAAIUAAE 7521 WARD AAANNGAAEAAAAIUAAF 7566 JONES AAANNGAAEAAAAIUAAG 7654 MARTIN AAANNGAAEAAAAIUAAH 7698 BLAKE AAANNGAAEAAAAIUAAI 7782 CLARK AAANNGAAEAAAAIUAAJ 7788 SCOTT AAANNGAAEAAAAIUAAK 7839 KING AAANNGAAEAAAAIUAAL 7844 TURNER AAANNGAAEAAAAIUAAM 7876 ADAMS AAANNGAAEAAAAIUAAN 7900 JAMES AAANNGAAEAAAAIUAAO 7902 FORD AAANNGAAEAAAAIUAAP 7934 MILLER AAANNGAAEAAAAIUAAQ 22 abcd AAANNGAAEAAAAIUAAR 0 abc AAANNGAAEAAAAIUAAZ
|
EMPNO ENAME ROWID ------ ---------- ------------------ 3 abc AAANNGAAEAAAAIUAAA 7369 SMITH AAANNGAAEAAAAIUAAB 7499 ALLEN AAANNGAAEAAAAIUAAN 7521 WARD AAANNGAAEAAAAIUAAS 7566 JONES AAANNGAAEAAAAIUAAT 7654 MARTIN AAANNGAAEAAAAIUAAU 7698 BLAKE AAANNGAAEAAAAIUAAV 7782 CLARK AAANNGAAEAAAAIUAAW 7788 SCOTT AAANNGAAEAAAAIUAAX 7839 KING AAANNGAAEAAAAIUAAY 7844 TURNER AAANNGAAEAAAAIUAAa 7876 ADAMS AAANNGAAEAAAAIUAAb 7900 JAMES AAANNGAAEAAAAIUAAc 7902 FORD AAANNGAAEAAAAIUAAd 7934 MILLER AAANNGAAEAAAAIUAAe 22 abcd AAANNGAAEAAAAIUAAf 0 abc AAANNGAAEAAAAIUAAg |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22528444/viewspace-681785/ ，如需转载，请注明出处，否则将追究法律责任。
