关于alter table move

alter table move主要有两方面的作用

1.用来移动table到其他表空间

2.用来减少table中的存储碎片，优化存储空间和性能

案例1：移动table到其他表空间

--alter table move
SQL> create table t tablespace system as select * from all_objects;


Table created.


SQL> select table_name,tablespace_name from user_tables where table_name='T';


TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              SYSTEM

在表上建立一个索引
SQL> create index idx_t_object_id on t(object_id);


Index created.


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


INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_OBJECT_ID  

当移动表到指定表空间时，用户在目标表空间必须有足够的表空间配额，否则会报错，比如

ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'

SQL> alter table t move tablespace tbs1;


Table altered.

移动到其他表空间后，索引就会失效。因为索引是通过rowid来定位记录的，当table迁移到其他表空间，或者进行

alter table ...move清理碎片的时候，索引失效。
SQL> select index_name,status from user_indexes where table_name='T';


INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_OBJECT_ID                UNUSABLE

重建索引
SQL> alter index idx_t_object_id rebuild tablespace tbs1;


Index altered.


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


INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_OBJECT_ID                VALID

else：alter table...move语句会在目标对象上放置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 表。

案例2：减少table存储碎片，优化性能

当有一些表经常性的执行删除而很少执行插入，表所在的segment将会存在较多的碎片，此时可以通过alter table move来

进行整理，降低高水位线，减少full table scan读取的block的数量并且可以提高data buffer cache的效率，因为缓存的都是

实打实的block

SQL> create table t as select * from all_objects;


Table created.


SQL> exec dbms_stats.gather_table_stats(user,'T');


PL/SQL procedure successfully completed.


SQL> exec show_space('T');  --此处使用tom的show_space过程来查看表的存储情况
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           1,025
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................             101
Unused Bytes............................         827,392
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           1,280
Last Used Block.........................              27


PL/SQL procedure successfully completed.

其中total blocks表示分配给表的总的block数，unusedblock表示位于高水位线以上的从未使用

过的block

SQL> select segment_name,bytes from user_extents where segment_name='T';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
     65536


T
     65536




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
     65536


T
     65536




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
     65536


T
     65536




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
     65536


T
     65536




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
     65536


T
     65536




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
   1048576


T
   1048576




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
   1048576


T
   1048576


T
   1048576




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
   1048576


T
   1048576


T
   1048576




24 rows selected.

此时T表有25个区的存储空间

删除T表的部分数据，验证存储空间变化

SQL> delete from t where object_id<35000;


34221 rows deleted.


SQL> commit;


Commit complete.

SQL> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................             479
Full Blocks        .....................             545
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................             101
Unused Bytes............................         827,392
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           2,560
Last Used Block.........................              27


PL/SQL procedure successfully completed.

相较于delete操作之前，并没有发生变化，说明此时表T已经存在存储空间碎片的情况，下面使用

alter table move，这里需要注意的一点是：

alter table move 操作来清理存储碎片，其实是读取t,然后在t表所在的表空间重建表t.等moce

操作完成之后删除原来的表。所以需要额外的存储空间开销，要求当前表空间的大小至少为

表的两倍

SQL> alter table t move;


Table altered.

SQL> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             545
Total Blocks............................             640
Total Bytes.............................       5,242,880
Total MBytes............................               5
Unused Blocks...........................              77
Unused Bytes............................         630,784
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           3,072
Last Used Block.........................              51


PL/SQL procedure successfully completed.

这个结果可以看出,hwm降下来了，并且还回收了一部分分配给表的block。

SQL> select segment_name,bytes from user_extents where segment_name='T';


SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
     65536


T
     65536




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
     65536


T
     65536




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
     65536


T
     65536




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
     65536


T
     65536




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
     65536


T
     65536




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
     65536


T
   1048576


T
   1048576




SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES
----------
T
   1048576


T
   1048576




20 rows selected.

相比较于之前25个区的存储空间，delete之后只剩了20个区的空间，所以在alter table move 的过程中

也会释放分配给表的block