前言：
对于row movement的说明，网上的一些资料：
1.在flashback中使用，当需要使用flashback table功能时，需要首先打开row mvoement的选项，否则使用该功能会报错。
2.Shrink Segment能帮助我们压缩数据段、整理数据碎片、降低高水位，以提高性能、节省空间。它也同样要求开启ROW MOVEMENT。
3.一般用于分区表，将row movement设置为enable,有可能发生行的物理移动，行的rowdi会变化，某一行更新时，如果更新的是分区列，并且更新后的列值不属于原来的这个分区，如果开启了这个选项，就会把这行从这个分区中delete掉，并加到更新后所属的分区。相当于一个隐式的触发器，但不会触发Insert/delete触发器。如果没有开启这个选项，更新时就会报错。
这里只分析第一点。

操作系统版本：

[oracle@oracle ~]$ uname -a
Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oracle ~]$ lsb_release -a
LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Release: 6.5
Codename: Santiago

数据库版本：

SYS@proc> SELECT * FROM V$VERSION WHERE ROWNUM=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

注意事项：
无法对sys用户的表进行flashback table table_name to scn xxxxxx,如下：

SYS@proc> create table t1 (id int);
Table created.
SYS@proc> insert into t1 values (1);
1 row created.
SYS@proc> commit;
Commit complete.
SYS@proc> select current_scn from v$database;
CURRENT_SCN
-----------
5828852
SYS@proc> delete from t1;
1 row deleted.
SYS@proc> commit;
Commit complete.
SYS@proc> select * from t1;
no rows selected
SYS@proc> select * from t1 as of scn 5828852;
ID
----------
1
SYS@proc> flashback table t1 to scn 5828852;
flashback table t1 to scn 5828852
*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS

构造环境：

SCOTT@proc> drop table tt purge;
Table dropped.
SCOTT@proc> create table tt (id int,name varchar2(2000)) tablespace users;
Table created.
SCOTT@proc> insert into tt values(1,rpad('a',1800,'+'));
1 row created.
SCOTT@proc> insert into tt values(2,rpad('b',1800,'+'));
1 row created.
SCOTT@proc> insert into tt values(3,rpad('c',1800,'+'));
1 row created.
SCOTT@proc> commit;
Commit complete.
SCOTT@proc> insert into tt values(4,rpad('d',1800,'+'));
1 row created.
SCOTT@proc> commit;
Commit complete.
SCOTT@proc> select id,substr(name,1,1),dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;
ID SU FILE# BLOCK# ROW#
---------- -- ---------- ---------- ----------
1 a 4 549 0
2 b 4 549 1
3 c 4 549 2
4 d 4 549 3
SCOTT@proc> delete from tt where id=2;
1 row deleted.
SCOTT@proc> commit;
Commit complete.
SCOTT@proc> select id,substr(name,1,1),dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;
ID SU FILE# BLOCK# ROW#
---------- -- ---------- ---------- ----------
1 a 4 549 0
3 c 4 549 2
4 d 4 549 3
SCOTT@proc> insert into tt values(5,rpad('e',1800,'+'));
1 row created.
SCOTT@proc> commit;
Commit complete.
SCOTT@proc> select id,substr(name,1,1),dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;
ID SU FILE# BLOCK# ROW#
---------- -- ---------- ---------- ----------
1 a 4 549 0
5 e 4 549 1
3 c 4 549 2
4 d 4 549 3

实验过程：

SCOTT@proc> select id,substr(name,1,1),rowid,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;
ID SU ROWID FILE# BLOCK# ROW#
---------- -- ------------------ ---------- ---------- ----------
1 a AAAV78AAEAAAAIlAAA 4 549 0
5 e AAAV78AAEAAAAIlAAB 4 549 1
3 c AAAV78AAEAAAAIlAAC 4 549 2
4 d AAAV78AAEAAAAIlAAD 4 549 3
SCOTT@proc> select current_scn from v$database;
CURRENT_SCN
-----------
6013384
SCOTT@proc> set time on
12:18:48 SCOTT@proc>
12:18:52 SCOTT@proc> delete from tt;
4 rows deleted.
12:19:03 SCOTT@proc> commit;
Commit complete.
12:19:04 SCOTT@proc> select id from tt as of scn 6013384;
ID
----------
1
5
3
4
12:19:21 SCOTT@proc> flashback table tt to scn 6013384;
flashback table tt to scn 6013384
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
12:19:56 SCOTT@proc> alter table tt enable row movement;
Table altered.
12:20:06 SCOTT@proc> flashback table tt to scn 6013384;
Flashback complete.
12:20:11 SCOTT@proc> select id,substr(name,1,1),rowid,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;
ID SU ROWID FILE# BLOCK# ROW#
---------- -- ------------------ ---------- ---------- ----------
1 a AAAV78AAEAAAAInAAA 4 551 0
5 e AAAV78AAEAAAAInAAB 4 551 1
3 c AAAV78AAEAAAAInAAC 4 551 2
4 d AAAV78AAEAAAAInAAD 4 551 3
12:20:16 SCOTT@proc>

结论：可以看到，开启了row movement之后，对表TT进行flashback to scn操作之后，每个行的rowid均发生了变化。对于该功能，有可能发生物理行的移动，所以需要开启row movement功能才能进行flashback操作。而在正常表中，表中行的rowid是不可以发生变化的。

ITPub博客

Oracle中flashback table功能为什么需要开启row movement功能

最新文章