oracle收缩表、清理碎片,释放空间
可以用来收缩段,消除空间碎片的方法有两种:
创新互联是一家专注网站建设、网络营销策划、重庆小程序开发、电子商务建设、网络推广、移动互联开发、研究、服务为一体的技术型公司。公司成立10年以来,已经为1000多家成都搬家公司各业的企业公司提供互联网服务。现在,服务的1000多家客户与我们一路同行,见证我们的成长;未来,我们一起分享成功的喜悦。
1.alter table table_name move
需要注意:
1)move操作会锁表。(如果是很小的表,可以在线做。如果是大表一定要注意,会长时间锁表,只能查询,影响正常业务运行。)
2)move操作会使索引失效,一定要rebuild。(因为move操作会改变一些记录的ROWID,所以MOVE之后索引会变为无效,需要REBUILD。)
2.使用shrink space
alter table table_name shrink space
前提条件
1) 必须启用行记录转移(enable row movement)
2) 仅仅适用于堆表,且位于自动段空间管理的表空间(堆表包括:标准表,分区表,物化视图容器,物化视图日志表)
优点:
提高缓存利用率,提高OLTP的性能
减少磁盘I/O,提高访问速度,节省磁盘空间
段收缩是在线的,索引在段收缩期间维护,不要求额外的磁盘空间
加参数
cascade:缩小表及其索引,并移动高水位线,释放空间
compact:仅仅是缩小表和索引,并不移动高水位线,不释放空间
如果在业务繁忙时做压缩,
可以使用alter table shrink space compact来对表格进行碎片整理,而不调整高水位线,之后再次调用alter table table_name shrink space来释放空间。
也可以使用alter table table_name shrink space cascade来同时对索引都进行收缩,这等同于同时执行alter index idxname shrink space。
方法一:move方式收缩表
1)创建一张新表test,并插入数据
SQL>
create
table
TEST (id
int
,
name
char
(2000)) tablespace users;
Table
created.
SQL>
insert
into
TEST
values
(1,
'aa'
);
SQL>
insert
into
TEST
values
(2,
'bb'
);
SQL>
insert
into
TEST
values
(3,
'cc'
);
--查看test表中rowid
SQL>
select
Dbms_Rowid.rowid_block_number(rowid)
from
TEST;
2) 删除表中部分数据,并再次查看表中rowid
SQL>
delete
from
TEST
where
mod(id,2)=1;
SQL>
select
Dbms_Rowid.rowid_block_number(rowid)
from
TEST;
3) 对表执行move操作
SQL>
alter
table
TEST
move
;
4)再次查看表中rowid
SQL>
select
Dbms_Rowid.rowid_block_number(rowid)
from
TEST;
小结: 1
move操作后,数据的rowid发生了改变,我们知道,index是通过rowid来获取数据行的,所以table上的index是必须要rebuild的。
5) 查看表中索引情况,此时索引为失效的
SQL> select index_name,status from user_indexes where index_name='TEST_INDEX';
INDEX_NAME STATUS
------------------------------ --------
TEST_INDEX UNUSABLE
6)在线重建索引
SQL>
alter
index
TEST_MOVE_INDEX rebuild online;
小结:2
move操作后,表中索引会失效
--查看锁情况
SQL> SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id;
SID USERNAME OBJECT_OWNER OBJECT_NAME LOCKED_MODE OS_USER_NAME
---------- -------------------- ---------------------- ------------------- ----------------------- -----------------------
33 YMM YMM TEST Exclusive (X) oracle
小结:3
--Exclusive (X) 是6号锁,独占锁。
--这就意味着,table在进行move操作时,我们只能对它进行select的操作。
也就是说当我们的一个session对table进行DML操作且没有commit时,在另一个session中是不能对这个table进行move操作的,
否则oracle会返回这样的错误信息:ORA-00054 。
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME EXTENTS BLOCKS INIT
------------------------ ---------- ---------- --------
TEST 3 1280 10
--TEST表初始分配了10M的空间,1280个BLOCKS。
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
----------------------- ---------- -------------------
TEST
--USER_TABLES视图显示有0个使用的BLOCKS,1280个空闲BLOCKS。
--向表中插入数据
SQL> insert into TEST select * from information;
SQL> analyze table TEST compute statistics;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME EXTENTS BLOCKS INIT
----------------------- ---------- ---------- ----------
TEST 3 1280 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST 1006 274
--插入数据后,分配的空间仍不变,因为10M还没使用完。显示使用了1006个BLOCKS,空闲274个BLOCKS。这时候的1006 BLOCKS即是高水位线。
SQL> commit;
SQL> select count(*) from test;
COUNT(*)
----------
122513
SQL> delete from test where rownum<=50000;
SQL> analyze table test compute statistics;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME EXTENTS BLOCKS INIT
------------------------- --------------- ---------- ----------
TEST 3 1280 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST 1006 274
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;
USED_BLOCKS
-----------
573
--这边可以看到,删掉部分数据后,仍然显示使用了1006个BLOCKS,高水位没变。但查询真正使用的BLOCK数只有573个。所以DELETE操作是不会改变HWM的。
SQL> alter table TEST move;
SQL> analyze table TEST compute statistics;
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------------- -------------- ---------- ----------
TEST 3 1280 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------- ---------- ------------
TEST 592 688
小结:4
--MOVE之后,HWM降低了,空闲块也上去了。
--但是分配的空间并没有改变,仍然是1280个BLOCKS。
方法二:shrink space方式收缩表
SQL> delete from test where rownum<=50000;
--首先设置允许行迁移
SQL> alter table TEST enable row movement;
SQL> alter table TEST shrink space;
SQL> analyze table TEST compute statistics; -->使用analyze更新统计信息后EMPTY_BLOCKS得到数据
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NAME EXTENTS BLOCKS INIT
------------------------ ---------------- ---------- ----------
TEST 1 600 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
TEST 592 8
--SHRINK SPACE真正做到了对段的压缩,包括初始分配的也压了,所以它是回收高水位线操作。
验证cascade与compact的差异
--删除一些数据
SQL> delete from test where rownum<8000;
SQL> alter table test shrink space compact; -->使用compact方式收缩表段
SQL> exec show_space('TEST','SCOTT');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 1
FS2 Blocks (25-50) ..................... 2
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 103
Full Blocks ............................ 14,214 --仅有的变化为14318-14214=104块,即完全填满的数据块减少了104块
Total Blocks............................ 14,488 --数据的总块数及总大小并没有减少,即未移动高水位线
Total Bytes............................. 118,685,696
Total MBytes............................ 113
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 16,521
Last Used Block......................... 147
PL/SQL procedure successfully completed.
SQL> alter table test shrink space cascade; -->使用cascade方式收缩
SQL> exec show_space('TEST','SCOTT');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 1
FS2 Blocks (25-50) ..................... 2
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ............................ 14,214
Total Blocks............................ 14,384 -->总块数及总大小均已减少
Total Bytes............................. 117,833,728
Total MBytes............................ 112
Unused Blocks........................... 4
Unused Bytes............................ 32,768
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 16,521
Last Used Block......................... 44
PL/SQL procedure successfully completed.
-->收缩之后索引依然有效
SQL> select OWNER,INDEX_NAME,STATUS from dba_indexes where TABLE_NAME='TEST';
OWNER INDEX_NAME STATUS
--------------- -------------------- ------------
SCOTT idx_test VALID
小结:
compact:仅仅是缩小表和索引,并不移动高水位线,不释放空间
cascade:缩小表及其索引,并移动高水位线,释放空间
语法总结:
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER INDEX IDXNAME SHRINK SPACE;
网站名称:oracle收缩表、清理碎片,释放空间
网页地址:http://myzitong.com/article/ihedjc.html