關于alter table move
alter table move 主要有兩方面的作用。
1、用來移動table 到其他表空間。
2、用來減少table 中的存儲碎片，優化存儲空間和性能。
針對第一點。
在system 表空間創建一個表。
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
在其上創建一個索引。
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
我們知道不應該把業務或者也管理無關的數據存放在system 表空間。所以我們把t 表移動到users 表空間去吧。
**********************************************************
注：用戶在目標表空間應該有足夠的表空間配額。
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.
現在索引變成無效了。因為索引是通過rowid 來定位記錄的，所以當table 遷移到其他表空間，或者進行alter table ...move 清理碎片的時候已經無效了。
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
還需要注意的問題：
在alter table...move... 語句的時候將會在目標對象上放置X鎖，如果需要移動的對象比較大，將會花費比較長的時候，所以X鎖的時間也會比較長。如果目標表上已經放置了一個X鎖，需要等到X的釋放，才能夠執行該語句。
***********************************************************************
注：對于IOT 表我們可以使用 alter table ... move ... 的online 子句。這時候目標表是可用的，可以在其上執行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.
在執行上述alter table t_emp move tablespace users online; 語句的時候，在其他會話中可以正常的訪問t_emp 表。
**************************************************************************
只對第二點。
有一些表經常性的執行刪除而很少執行插入，這時候表所在的segment 中將會存在較多的碎片，我們可以使用alter table move 進行整理，這樣可以降低高水位線，減少full table scan讀取的block 的數量并且可以提高data buffer cache 的效率，因為緩存的data block 實打實的存在內容。
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.
下面使用的這個存儲過程我是從asktom 下載的procedure code 創建的。
Total Blocks 表示分配給表的總的blocks 數。
Unused Blocks 表示位于高水位線以上的從未使用的數據塊個數。
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 并不會釋放存儲空間，不會降低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.
執行alter table ... move 清理表中的存儲碎片。實質上是讀取t，然后在t 表所在的表空間重建表t 。等move 操作完成以后刪除原來的表.所以需要額外的
存儲空間開銷，所以當前表空間的大小至少為表大小的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 降下來了，并且還回收了一部分分配給表的blocks 。
************************************************************************
注：
有一種說法是 alter table ... move 可以降低表的hwm,但是不能夠釋放已經分配給他的blocks ，可能這樣明明看到Total Blocks 減小了。我們還可以通過查看user_extents 來確定。
沒有執行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.
執行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.
