本文共 5488 字，大约阅读时间需要 18 分钟。
alter table move 主要有两方面的作用。 2、用来减少table 中的存储碎片，优化存储空间和性能。 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 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 表示位于高水位线以上的从未使用的数据块个数。 SQL> set serveroutput on SQL> exec show_space('T') Free Blocks.............................0Total Blocks............................384 Total Bytes.............................3145728Unused 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. 转载地址：http://zuuix.baihongyu.com/