alter table table_name enable row movement，altertable_name

alter table table_name enable row movement，altertable_name

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代碼  

1. SQL> ALTER TABLE CITY_OFFICES ENABLE ROW MOVEMENT;  
2. Table altered.  
3. SQL> FLASHBACK TABLE CITY_OFFICES  
4.   2    TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '05' minute);  
5. Flashback complete.  
6. SQL> SELECT * FROM CITY_OFFICES;  
7. OFFICE_NUMBER CITY_ID      OFFICE_NAME  
8. ------------- ------------ ------------------------------  
9.             1 282          DENVER  
10.             2 282          DENVER TECH CTR  
11.             3 282          DENVER WEST  
12.             4 283          BROOMFIELD  

 

If the table has row movement disabled, why is it you can drop the table and flashback the table to before the drop without row movement being enabled? 

 

Java代碼  

1. SQL> ALTER TABLE CITY_OFFICES DISABLE ROW MOVEMENT;  
2. Table altered.  
3. SQL> DROP TABLE CITY_OFFICES;  
4. Table dropped.  
5. SQL> FLASHBACK TABLE CITY_OFFICES TO BEFORE DROP;  
6. Flashback complete.  

 

 

3:回收空間

在收縮空間時，也會造成行的移動，如下

 

SQL> alter table easy disable row movement;

 

Table altered.

 

SQL> alter table easy shrink space;

alter table easy shrink space

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

 

 

SQL> alter table easy enable row movement;

 

Table altered.

 

SQL> alter table easy shrink space;

 

Table altered.

 

Shrink space操作（without  compact）會導致所有已經開啟的遊標失效，因此需要謹慎使用。