ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于alter table move

关于alter table move

原创 Linux操作系统 作者:pingley 时间:2012-06-10 22:11:17 0 删除 编辑
关于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 表示位于高水位线以上的从未使用的数据块个数。
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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26110315/viewspace-732418/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    169
  • 访问量
    715392