關(guān)于alter table move
alter table move 主要有兩方面的作用。
1、用來(lái)移動(dòng)table 到其他表空間。
2、用來(lái)減少table 中的存儲(chǔ)碎片，優(yōu)化存儲(chǔ)空間和性能。
針對(duì)第一點(diǎn)。
在system 表空間創(chuàng)建一個(gè)表。
SQL> create table t as select * from dba_objects;
Table created.
SQL> select table_name,tablespace_name
2 from user_tables
3 where table_name = 'T';
TABLE_NAME TABLESPACE_NAME
--------------- ---------------
T SYSTEM
在其上創(chuàng)建一個(gè)索引。
SQL> create index t_idx on t (object_id);
Index created.
SQL> select index_name,status
2 from user_indexes
3 where table_name = 'T';
INDEX_NAME STATUS
------------------------------------------------------------ ---------
T_IDX VALID
我們知道不應(yīng)該把業(yè)務(wù)或者也管理無(wú)關(guān)的數(shù)據(jù)存放在system 表空間。所以我們把t 表移動(dòng)到users 表空間去吧。
**********************************************************
注：用戶在目標(biāo)表空間應(yīng)該有足夠的表空間配額。
SQL> conn t/test
Connected.
SQL> create table emp as select * from hr.employees;
Table created.
SQL> alter table emp move tablespace system;
alter table emp move tablespace system
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'
**********************************************************
SQL> alter table t move tablespace users;
Table altered.
現(xiàn)在索引變成無(wú)效了。因?yàn)樗饕峭ㄟ^(guò)rowid 來(lái)定位記錄的，所以當(dāng)table 遷移到其他表空間，或者進(jìn)行alter table ...move 清理碎片的時(shí)候已經(jīng)無(wú)效了。
SQL> select index_name,status
2 from user_indexes
3 where table_name = 'T';
INDEX_NAME STATUS
------------------------------------------------------------ ----------
T_IDX UNUSABLE
重建index。
SQL> alter index t_idx rebuild tablespace users;
Index altered.
SQL> select index_name,tablespace_name,status
2 from user_indexes
3 where table_name = 'T';
INDEX_NAME TABLESPACE_NAME STATUS
-------------------- --------------- ----------------
T_IDX USERS VALID
還需要注意的問(wèn)題：
在alter table...move... 語(yǔ)句的時(shí)候?qū)?huì)在目標(biāo)對(duì)象上放置X鎖，如果需要移動(dòng)的對(duì)象比較大，將會(huì)花費(fèi)比較長(zhǎng)的時(shí)候，所以X鎖的時(shí)間也會(huì)比較長(zhǎng)。如果目標(biāo)表上已經(jīng)放置了一個(gè)X鎖，需要等到X的釋放，才能夠執(zhí)行該語(yǔ)句。
***********************************************************************
注：對(duì)于IOT 表我們可以使用 alter table ... move ... 的online 子句。這時(shí)候目標(biāo)表是可用的，可以在其上執(zhí)行select,DML 操作。
SQL> create table t_emp(
2 employee_id number(6),
3 last_name varchar2(20),
4 first_name varchar2(25),
5 constraint t_emp_pk primary key(employee_id))
6 organization index;
Table created.
SQL> insert into t_emp select employee_id,last_name,first_name
2 from hr.employees;
108 rows created.
SQL> commit;
Commit complete.
SQL> alter table t_emp move tablespace users online;
Table altered.
在執(zhí)行上述alter table t_emp move tablespace users online; 語(yǔ)句的時(shí)候，在其他會(huì)話中可以正常的訪問(wèn)t_emp 表。
**************************************************************************
只對(duì)第二點(diǎn)。
有一些表經(jīng)常性的執(zhí)行刪除而很少執(zhí)行插入，這時(shí)候表所在的segment 中將會(huì)存在較多的碎片，我們可以使用alter table move 進(jìn)行整理，這樣可以降低高水位線，減少full table scan讀取的block 的數(shù)量并且可以提高data buffer cache 的效率，因?yàn)榫彺娴膁ata block 實(shí)打?qū)嵉?/span>存在內(nèi)容。
SQL> create table t as select object_id,object_name
2 from dba_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
下面使用的這個(gè)存儲(chǔ)過(guò)程我是從asktom 下載的procedure code 創(chuàng)建的。
Total Blocks 表示分配給表的總的blocks 數(shù)。
Unused Blocks 表示位于高水位線以上的從未使用的數(shù)據(jù)塊個(gè)數(shù)。
ref:http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:231414051079
SQL> set serveroutput on
SQL> exec show_space('T')
Free Blocks.............................0
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................68
Unused Bytes............................557056Last Used Ext FileId....................1
Last Used Ext BlockId...................134528
Last Used Block.........................60
PL/SQL procedure successfully completed.
SQL> delete from t
2 where object_id < 35000;
34549 rows deleted.
SQL> commit;
Commit complete.
SQL> exec show_space('T')--delete 并不會(huì)釋放存儲(chǔ)空間，不會(huì)降低hwm。
Free Blocks.............................161
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................68
Unused Bytes............................557056
Last Used Ext FileId....................1
Last Used Ext BlockId...................134528
Last Used Block.........................60
PL/SQL procedure successfully completed.
執(zhí)行alter table ... move 清理表中的存儲(chǔ)碎片。實(shí)質(zhì)上是讀取t，然后在t 表所在的表空間重建表t 。等move 操作完成以后刪除原來(lái)的表.所以需要額外的
存儲(chǔ)空間開(kāi)銷，所以當(dāng)前表空間的大小至少為表大小的2倍。
SQL> alter table t move;
Table altered.
SQL> exec show_space('T')
Free Blocks.............................0
Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................101
Unused Bytes............................827392
Last Used Ext FileId....................1
Last Used Ext BlockId...................134656
Last Used Block.........................27
PL/SQL procedure successfully completed.
hwm 降下來(lái)了，并且還回收了一部分分配給表的blocks 。
************************************************************************
注：
有一種說(shuō)法是 alter table ... move 可以降低表的hwm,但是不能夠釋放已經(jīng)分配給他的blocks ，可能這樣明明看到Total Blocks 減小了。我們還可以通過(guò)查看user_extents 來(lái)確定。
沒(méi)有執(zhí)行delete 和alter table ... move 以前的情形。
SQL> select segment_name,bytes from
2 user_extents
3 where segment_name = 'T';
SEGMENT_NAME BYTES
-------------------- ----------
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
SEGMENT_NAME BYTES
-------------------- ----------
T 65536
T 65536
T 65536
T 65536
T 65536
T 1048576
T 1048576
18 rows selected.
執(zhí)行delete 和alter table ... move 以后的情形。
SQL> select segment_name,bytes from
2 user_extents
3 where segment_name = 'T';
SEGMENT_NAME BYTES
-------------------- ----------
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
T 65536
SEGMENT_NAME BYTES
-------------------- ----------
T 65536
T 65536
T 65536
T 65536
T 65536
T 1048576
17 rows selected.
