【轉】關於oracle中Move機制的一點探討

從8i開始，oracle開始提供Move的命令。我們通常使用這個命令，將一個table segment從一個tablespace移動到另一個tablespace。

Move實際上是在block之間物理的copy資料，那麼，我們可以透過這種方式來降低table的HWM。我們先透過一個實驗來看看move是如何移動資料的。建立table TEST_HWM，insert一些資料：

SQL> create table TEST_HWM (id int ,name char(2000)) tablespace hwm;

Table created

我們往table TEST_HWM 中insert如下資料：

insert into TEST_HWM values (1,'aa');

insert into TEST_HWM values (2,'bb');

insert into TEST_HWM values (2,'cc');

insert into TEST_HWM values (3,'dd');

insert into TEST_HWM values (4,'ds');

insert into TEST_HWM values (5,'dss');

insert into TEST_HWM values (6,'dss');

insert into TEST_HWM values (7,'ess');

insert into TEST_HWM values (8,'es');

insert into TEST_HWM values (9,'es');

insert into TEST_HWM values (10,'es');

我們來看看這個table的rowid和block的ID和資訊：

SQL> select rowid , id,name from TEST_HWM;

                                                 

ROWID                           ID     NAME

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

AAAH7JAALAAAAAUAAA                1     aa

AAAH7JAALAAAAAUAAB                2     bb

AAAH7JAALAAAAAUAAC                2     cc

AAAH7JAALAAAAAVAAA                3     dd

AAAH7JAALAAAAAVAAB                4     ds

AAAH7JAALAAAAAVAAC                5     dss

AAAH7JAALAAAAAWAAA                6     dss

AAAH7JAALAAAAAWAAB                7     ess

AAAH7JAALAAAAAWAAC                8     es

AAAH7JAALAAAAAXAAA                9     es

AAAH7JAALAAAAAXAAB               10     es

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name='TEST_HWM' ;

EXTENT_ID    FILE_ID RELATIVE_FNO   BLOCK_ID     BLOCKS

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

         0         11           11         19          5

這裡，簡單地介紹一下rowid的相關知識：

ROWID 在磁碟上需要10 個位元組的儲存空間並使用18 個字元來顯示它包含下列元件：

資料物件編號：每個資料物件如表或索引在建立時都分配有此編號，並且此編號在資料庫中是唯一的；

相關檔案編號：此編號對於一個表空間中的每個檔案是唯一的；

塊編號：表示包含此行的塊在檔案中的位置；

行編號：標識塊頭中行目錄位置的位置；

在內部資料物件編號需要32 位，相關檔案編號需要10 位，塊編號需要22，位行編號需要16 位，加起來總共是80 位或10 個位元組，ROWID 使用以64 為基數的編碼方案來顯示該方案將六個位置用於資料物件，編號三個位置用於相關檔案編號六個位置用於塊編號三個位置用於行編號以64 為基數的編碼方案使用字元A-Z a-z 0-9 + 和/共64 個字元，如下例所示：

AAAH7J AAL AAAAAU AAA

在本例中

AAAH7J   是資料物件編號

AAL      是相關檔案編號

AAAAAU 是塊編號

AAA       是行編號

那麼，我們根據資料的rowid，可以看出這11行資料分佈在AAAAAU，AAAAAV，AAAAAW，AAAAAX這四個block中。

然後我們從table TEST_HWM中delete一些資料：

delete from TEST_HWM where id = 2;

delete from TEST_HWM where id = 4;

delete from TEST_HWM where id = 3;

delete from TEST_HWM where id = 7;

delete from TEST_HWM where id = 8;

delete from TEST_HWM where id = 9;

我們在來看看這個table的rowid和block的ID和資訊：

SQL> select rowid , id,name from TEST_HWM;

ROWID                      ID NAME

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

AAAH7JAALAAAAAUAAA       1 aa

AAAH7JAALAAAAAVAAC       5 dss

AAAH7JAALAAAAAWAAA      6 dss

AAAH7JAALAAAAAXAAB       10 es

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2   from dba_extents where segment_name='TEST_HWM' ;

EXTENT_ID    FILE_ID RELATIVE_FNO BLOCK_ID     BLOCKS

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

         0         11           11         19          5

在這裡，我們可以看到，資料的rowid沒有發生改變，我們根據資料的rowid，可以看出這4行資料依然分佈在AAAAAU，AAAAAV，AAAAAW，AAAAAX這四個block中。

接下來我們對table TEST_HWM進行move的操作，然後再來觀察rowid，blockid的資訊：

SQL> alter table TEST_HWM move;

Table altered

SQL> select rowid,id,name from HWM;

ROWID                    ID NAME

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

AAAH7NAALAAAANrAAA       1 aa

AAAH7NAALAAAANrAAB       5 dss

AAAH7NAALAAAANrAAC       6 dss

AAAH7NAALAAAANsAAA       10 es

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2   from dba_extents where segment_name=' TEST_HWM ' ;

EXTENT_ID    FILE_ID RELATIVE_FNO   BLOCK_ID     BLOCKS

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

         0         11           11        874          5

我們可以看到，對table TEST_HWM進行move後，該table所在blockid發生了改變，那麼資料的rowid自然也發生了改變。從上面的結果，我們可以看到，現在table TEST_HWM 的資料分佈在AAAANr，AAAANs兩個block中了。但是這四行資料的rowid的順序來看，這四行資料在table中的儲存順序並沒有發生改變。這也就證明了，move是在block之間物理的copy資料。

我們再來看看move操作對於table的HWM的位置有什麼變化，我們同樣使用系統檢視all_objects來建立測試table my_objects，然後delete前9999行資料：

SQL> create table my_objects tablespace HWM

2        as select * from all_objects;

SQL> delete from my_objects where rownum<10000;

9999 rows deleted

SQL> select count(*) from my_objects;

COUNT(*)

----------

     21015

SQL> exec show_space(p_segname => 'MY_OBJECTS',p_owner => 'DLINGER',p_type => 'TABLE');

Total Blocks............................425

Total Bytes.............................3481600

Unused Blocks...........................3

Unused Bytes............................24576

Last Used Ext FileId....................11

Last Used Ext BlockId...................1294

Last Used Block.........................2

這裡HWM=425 - 3 + 1 = 423

然後對table MY_OBJECTS進行move操作：

SQL> alter table MY_OBJECTS move;

表已更改。

SQL> exec show_space(p_segname => 'MY_OBJECTS',p_owner => 'DLINGER',p_type => 'TABLE');

Total Blocks............................290

Total Bytes.............................2375680

Unused Blocks...........................1

Unused Bytes............................8192

Last Used Ext FileId....................11

Last Used Ext BlockId...................1584

Last Used Block.........................4

我們可以看到，table MY_OBJECTS的HWM從423移動到290，table的HWM降低了！

我們還可以使用別的方法來降低table的HWM，比如CTAS，insert into 等，那麼move操作對redo logo的寫和其他的方式比較起來是相對較少的，我們在這裡就不列出把具體的實驗結果了，大家有興趣的可以自己動手來證實一下。

上面我們討論了move的執行機制和如何使用move降低table的HWM，這裡，我們補充說明move的另外一些用法，以及使用move時的一些要注意的問題。

Move的一些用法

以下是alter table 中move子句的完整語法，我們介紹其中的幾點：

MOVE [ONLINE]   [segment_attributes_clause]   [data_segment_compression] [index_org_table_clause] [ { LOB_storage_clause | varray_col_properties }    [ { LOB_storage_clause | varray_col_properties } ]... ] [parallel_clause]

a. 我們可以使用move將一個table從當前的tablespace上移動到另一個tablespace上，如：

alter table t move tablespace tablespace_name;

b. 我們還可以用move來改變table已有的block的儲存引數,如：

alter table t move storage (initial 30k next 50k);

另外，move操作也可以用來解決table中的行遷移的問題。

使用move的一些注意事項

a.       table上的index需要rebuild：

在前面我們討論過，move操作後，資料的rowid發生了改變，我們知道，index是透過rowid來fetch資料行的，所以，table上的index是必須要rebuild的。

SQL> create index i_my_objects on my_objects (object_id);

Index created

SQL> alter table my_objects move;

Table altered

SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';

INDEX_NAME                     STATUS

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

I_MY_OBJECTS                   UNUSABLE

從這裡可以看到，當table MY_OBJECTS進行move操作後，該table 上的inedx的狀態為UNUSABLE，這時，我們可以使用alter index I_MY_OBJECTS rebuild online的命令，對index I_MY_OBJECTS進行線上rebuild。

b.      move時對table的鎖定

當我們對table MY_OBJECTS進行move操作時，查詢v$locked_objects檢視可以發現，table MY_OBJECTS上加了exclusive lock：

SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;

OBJECT_ID SESSION_ID ORACLE_USERNAME   LOCKED_MODE

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

     32471          9 DLINGER                      6

SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';

OBJECT_ID

----------

     32471

這就意味著，table在進行move操作時，我們只能對它進行select的操作。反過來說，當我們的一個session對table進行DML操作且沒有commit時，在另一個session中是不能對這個table進行move操作的，否則oracle會返回這樣的錯誤資訊：ORA-00054: 資源正忙，要求指定 NOWAIT。

c.      關於move時空間使用的問題：

當我們使用alter table move來降低table的HWM時，有一點是需要注意的，這時，當前的tablespace中需要有1倍於table的空閒空間以供使用：

SQL> CREATE TABLESPACE TEST1

   2   DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\TEST1.dbf' SIZE

   3   UNIFORM. SIZE 128K ;

SQL> create table my_objects   tablespace test1 as select * from all_objects;

表已建立。

SQL> select bytes/1024/1024 from user_segments where segment_name='MY_OBJECTS';

BYTES/1024/1024

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

           3.125

SQL> alter table MY_OBJECTS move;

alter table MY_OBJECTS move

             *

ERROR 位於第 1 行:

ORA-01652: 無法透過16（在表空間TEST1中）擴充套件 temp 段

SQL> ALTER DATABASE

   2   DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\TEST1.DBF' RESIZE  ;

資料庫已更改。

SQL> alter table MY_OBJECTS move;

表已更改。