关于altertablemove

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.