oracle enable row movement,alter table table_name enable row movement

Row movement从字面意思解释为行移动.默认情况下,oracle数据块中的一行其生命周期内是不会发生移动的,即其rowid不会发生改变.但是在某些情景下，我们希望行的rowid可以发生变化，这时候我们就需要启动表的row

movement特性。

启用row movement特性，使用如下语句：

Alter table table_name enable row movement;

通查在三种情景下，需要启用row movement。

1:分区表

当我们允许分区表的分区键是可更新的时候，如果分区键的更新导致当前修改的数据条目需要移动到其他分区，此时就会发生rowid的改变。

SQL> create table city_office(office_number number,city_id varchar2(10))

2  partition by list(city_id)

3  (

4  partition p1 values(‘1‘),

5  partition p2 values(‘2‘),

6  partition p3 values(‘3‘));

Table created.

SQL> insert into city_office values(1,‘1‘);

1 row created.

SQL> insert into city_office values(2,‘2‘);

1 row created.

SQL> insert into city_office values(3,‘3‘);

1 row created.

SQL> insert into city_office values(4,‘1‘);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from city_office partition(p1);

OFFICE_NUMBER CITY_ID

------------- ----------

1 1

4 1

SQL> select * from city_office partition(p2);

OFFICE_NUMBER CITY_ID

------------- ----------

2 2

SQL> update city_office set city_id=2 where office_number=4;

update city_office set city_id=2 where office_number=4

*

ERROR at line 1:

ORA-14402: updating partition key column would cause a partition change

SQL> alter table city_office enable row movement;

Table altered.

SQL> update city_office set city_id=2 where office_number=4;

1 row updated.

SQL>

SQL>

SQL> select * from city_office partition(p1);

OFFICE_NUMBER CITY_ID

------------- ----------

1 1

SQL> select * from city_office partition(p2);

OFFICE_NUMBER CITY_ID

------------- ----------

2 2

4 2

2:闪回操作

在闪回操作时，我们同样需要开启表的row movement特性。

SQL> alter table city_office disable row movement;

Table altered.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

1291848

SQL> select count(*) from city_office;

COUNT(*)

----------

4

SQL> delete from city_office where city_id=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> flashback table city_office to scn 1291848;

flashback table city_office to scn 1291848

*

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

SQL> alter table city_office enable row movement;

Table altered.

SQL> flashback table city_office to scn 1291848;

Flashback complete.

SQL> select count(*) from city_office;

COUNT(*)

----------

4

为什么flashback table会造成rowid的改变那？通过下面的实验来研究一下：

SQL> create table easy(id number);

Table created.

SQL> insert into easy values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table easy enable row movement;

Table altered.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

1292223

SQL> update table easy set id = 2;

update table easy set id = 2

*

ERROR at line 1:

ORA-00903: invalid table name

SQL> update easy set id = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter session set tracefile_identifier = ‘ee‘;

Session altered.

SQL> alter session set sql_trace=true;

Session altered.

SQL> flashback table easy to scn 1292223;

Flashback complete.

SQL> select * from sys_temp_fbt ;

SCHEMA     OBJECT_NAME             OBJECT# RID                            A

---------- -------------------- ---------- ------------------------------ -

EASY       EASY                      76906 AAASxqAAGAAAAC0AAA             D

EASY       EASY                      76906 AAASxqAAGAAAAC0AAA             I

通过查看trace文件，我们可以发现oracle

flashback table是通过一个临时表sys_temp_fbt来实现的。trace文件中的部分内容如下：

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

SQL ID: fbk9spd3mwsfg Plan Hash: 3764894756

INSERT /*+ APPEND */ into SYS_TEMP_FBT SELECT /*+ FBTSCAN FULL(S) PARALLEL(S,

DEFAULT) */ :1, :2, :3, rowid, SYS_FBT_INSDEL FROM "EASY"."EASY" as of SCN

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

SQL ID: 7fh8jd0y92mv9 Plan Hash: 1584303325

DELETE /*+ BYPASS_UJVC */ FROM (SELECT /*+ ORDERED USE_NL(S) PARALLEL(S,

DEFAULT) PARALLEL(T,DEFAULT) */ S.rowid FROM SYS_TEMP_FBT T, "EASY"."EASY"

S

WHERE

T.rid = S.rowid and T.action = ‘D‘ and T.object#  = : 1) V

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

SQL ID: gpj0cz9m8173z Plan Hash: 1430220360

INSERT /*+ PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ INTO "EASY"."EASY"

SELECT /*+ USE_NL(S) ORDERED PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */

S.* FROM SYS_TEMP_FBT T , "EASY"."EASY" as of SCN :1 S WHERE T.rid =

S.rowid and T.action = ‘I‘ and T.object# = :2

由此可见，oracle是通过SYS_TEMP_FBT进行删除操作，而后进行插入操作，因此行的rowid有可能发生改变、

.

Java代码