关于alter table move

Oracle-Admin 专栏收录该内容
31 篇文章 0 订阅

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

  • 2
    点赞
  • 0
    评论
  • 12
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值