关于alter table move
发表于:2024-10-04 作者:千家信息网编辑
千家信息网最后更新 2024年10月04日,关于alter table movealter table move 主要有两方面的作用。1、用来移动table 到其他表空间。2、用来减少table 中的存储碎片,优化存储空间和性能。针对第一点。在
千家信息网最后更新 2024年10月04日关于alter table move关于alter table movealter 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:231414051079SQL> 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.
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:231414051079SQL> 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.
空间
存储
时候
目标
碎片
索引
语句
分配
移动
大小
对象
情形
所在
数据
水位
水位线
实打实
业务
个数
中将
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
安徽视频分布式存储数据库
宽城区有名的网络技术服务有哪些
数据库同时搜所有表
火河科技智能门锁服务器异常
教学评估管理系统数据库设计
mysql数据库安装详细
远程管理阿里云服务器
我的世界网易搞免费服务器
网络安全的利弊论文
小红书数据库营销案例分析
广州2u服务器散热器生产厂家
网络安全知识投稿
csgo上海服务器在哪
办公软件开发公司怎么样
四川网络安全专家库
权限管理 数据库设计
数据库中删除数据用哪个命令
怎样运行一个自制的服务器
数据库系统设计点名
国内外空间数据库文献
通信网络安全防护回头看
苏州科网络技术有限公司
dota数据库
怀旧服新服务器刷新时间
网络安全策略在哪里设置
turtle数据库颜色
好听的互联网科技公司名字
南京苏博特软件开发待遇
nx网络安全专家
电视网络正常服务器无法连接