deallocate unused :仅适用于释放HWM高水位以上的空间，而无法释放高水位以下的空间；比如对表预分配的空间
使用说明和方法，官方文档有说明，如下：
Use the deallocate_unused_clause to explicitly deallocate unused space at the end of
a database object segment and make the space available for other segments in the
tablespace.
You can deallocate unused space using the following statements:
■ ALTER CLUSTER (see ALTER CLUSTER on page 10-5)
■ ALTER INDEX: to deallocate unused space from the index, an index partition, or an
index subpartition (see ALTER INDEX on page 10-78)
■ ALTER MATERIALIZED VIEW: to deallocate unused space from the overflow segment
of an index-organized materialized view (see ALTER MATERIALIZED VIEW on
page 11-3)
■ ALTER TABLE: to deallocate unused space from the table, a table partition, a table
subpartition, the mapping table of an index-organized table, the overflow segment
of an index-organized table, or a LOB storage segment (see ALTER TABLE on
page 12-2)
一、测试环境：
Oracle11.2.0.4
使用admin用户在test表空间新建一张测试分区表，并提前对子分区预分配一些空间，然后插入少量的数据，最后使用：alter table .....deallocate unused;来释放未使用的空间
二、开始测试
①：查看测试环境表空间使用情况：
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set
linesize 2500
SQL>
set
pagesize 300
SQL>
select
a.tablespace_name
"表空间名称"
,100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2)
"占用率(%)"
,round(a.bytes_alloc / 1024 / 1024, 2)
"容量(M)"
,round(nvl(b.bytes_free, 0) / 1024 / 1024, 2)
"空闲(M)"
,round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2)
"使用(M)"
,to_char(sysdate,
'yyyy-mm-dd hh24:mi:ss'
)
"采样时间"
from (
select
f.tablespace_name,
sum
(f.bytes) bytes_alloc,
sum
(decode(f.autoextensible,
'YES'
, f.maxbytes,
'NO'
, f.bytes)) maxbytes from dba_data_files f group by tablespace_name) a,(
select
f.tablespace_name,
sum
(f.bytes) bytes_free from dba_free_space f group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by 2 desc;
??????????????? ?????????(%) ??????(M) ??????(M) ??????(M) ????????????
------------------------------ ------------ ---------- ---------- ---------- -------------------
SYSAUX 94.41 510 28.5 481.5 2017-08-07 17:01:20
SYSTEM 93.24 800 54.06 745.94 2017-08-07 17:01:20
USERS 26.25 5 3.69 1.31 2017-08-07 17:01:20
UNDOTBS1 17.97 470 385.56 84.44 2017-08-07 17:01:20
TEST .05 20480 20470 10 2017-08-07 17:01:20
|
|
1
2
|
SQL> show user
USER is
"ADMIN"
----测试用的用户
|
②：创建测试分区表：test_emp
|
1
2
3
4
|
SQL> create table test_emp (TMP_UPSTATE_CASEKEY char(14),TMP_NUM_STATUS_ID number(38),updated_date
date
)
2 partition by range(TMP_NUM_STATUS_ID)
3 (partition pt_1 values
less
than(1000000) ) nologging;
Table created.
|
③：给pt_1分区表预分配10G的空间：
|
1
2
|
SQL> alter table test_EMP modify PARTITION pt_1 ALLOCATE EXTENT (size 10240m);
Table altered.
|
④：收集该分区表的统计信息，并查看该表的block块占用情况
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
exec
dbms_stats.gather_table_stats(ownname =>
'ADMIN'
,tabname =>
'TEST_EMP'
,cascade=>TRUE);
PL
/SQL
procedure successfully completed.
SQL>
select
B.SEGMENT_NAME, B.blocks,B.blocks * 8096 / 1024 / 1024, A.BLOCKS,A.blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, USER_SEGMENTS B WHERE TABLE_NAME =
'TEST_EMP'
AND A.TABLE_NAME = B.SEGMENT_NAME;
SEGMENT_NAME BLOCKS B.BLOCKS*8096
/1024/1024
BLOCKS A.BLOCKS*8096
/1024/1024
EMPTY_BLOCKS
--------------------------------------------------------------------------------- ---------- ----------------------- ---------- ----------------------- ------------
TEST_EMP 1315840 10159.5313 0 0 0
SQL> SELECT segment_name, SUM(bytes) / 1024 / 1024 Mbytes FROM dba_segments WHERE PARTITION_NAME =
'PT_1'
GROUP BY segment_name;
SEGMENT_NAME MBYTES
--------------------------------------------------------------------------------- ----------
TEST_EMP 10288
BIN$VibHVCPfDL
/gU8gCqMDDfw
==$0 8
|
---从上面可以看出，test_emp分区表的大小是10G，而占用的blocks有1315840个；
⑤：向分区表插入一些数据，并查看该表的大小
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL> create or replace procedure proc_casekey_upstate
2 as
3 casekey char(14);
4 begin
5
for
i
in
1..10000 loop
6 casekey :=
'TMP'
||lpad(i,7,0);
7 insert into test_emp values(casekey, 1, sysdate);
8 end loop;
9 commit;
10 end;
11 /
Procedure created.
SQL>
exec
proc_casekey_upstate; ---执行存储过程插入数据
PL
/SQL
procedure successfully completed.
SQL>
exec
dbms_stats.gather_table_stats(ownname =>
'ADMIN'
,tabname =>
'TEST_EMP'
,cascade=>TRUE); ---收集该表的统计信息
PL
/SQL
procedure successfully completed.
SQL>
select
B.SEGMENT_NAME, B.blocks,B.blocks * 8096 / 1024 / 1024, A.BLOCKS,A.blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, USER_SEGMENTS B WHERE TABLE_NAME =
'TEST_EMP'
AND A.TABLE_NAME = B.SEGMENT_NAME;
SEGMENT_NAME BLOCKS B.BLOCKS*8096
/1024/1024
BLOCKS A.BLOCKS*8096
/1024/1024
EMPTY_BLOCKS
--------------------------------------------------------------------------------- ---------- ----------------------- ---------- ----------------------- ------------
TEST_EMP 1315840 10159.5313 46 .355163574 0
SQL> SELECT segment_name, SUM(bytes) / 1024 / 1024 Mbytes FROM dba_segments WHERE PARTITION_NAME =
'PT_1'
GROUP BY segment_name;
SEGMENT_NAME MBYTES
--------------------------------------------------------------------------------- ----------
TEST_EMP 10288
BIN$VibHVCPfDL
/gU8gCqMDDfw
==$0 8
|
--注意：从上面可以看出，该表的大小是10G，但是该表占用blocks只有46个，显然 有很多空间没有被使用，
⑥：释放未被使用的空间（我对该操作过了10046事件，在实际操作中只需执行：alter table test_emp modify partition pt_1 deallocate unused; ）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> alter session
set
tracefile_identifier=
'10046'
;
SQL> alter session
set
events
'10046 trace name context forever,level 12'
; ---开启10046事件
Session altered.
SQL>
SQL> alter table test_emp modify partition pt_1 deallocate unused; ---执行该命令释放子分区未被使用的空间；
Table altered.
SQL> alter session
set
events
'10046 trace name context off'
; ---关闭10046事件
Session altered.
SQL>
select
value from
v
$diag_info where name=
'Default Trace File'
;
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/dbs/dbs/trace/dbs_ora_3263_10046
.trc
|
⑦：验证空间是否被释放：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
select
B.SEGMENT_NAME, B.blocks,B.blocks * 8096 / 1024 / 1024, A.BLOCKS,A.blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, USER_SEGMENTS B WHERE TABLE_NAME =
'TEST_EMP'
AND A.TABLE_NAME = B.SEGMENT_NAME;
SEGMENT_NAME BLOCKS B.BLOCKS*8096
/1024/1024
BLOCKS A.BLOCKS*8096
/1024/1024
EMPTY_BLOCKS
--------------------------------------------------------------------------------- ---------- ----------------------- ---------- ----------------------- ------------
TEST_EMP 1024 7.90625 46 .355163574 0
SQL> SELECT segment_name, SUM(bytes) / 1024 / 1024 Mbytes FROM dba_segments WHERE PARTITION_NAME =
'PT_1'
GROUP BY segment_name;
SEGMENT_NAME MBYTES
--------------------------------------------------------------------------------- ----------
TEST_EMP 16
BIN$VibHVCPfDL
/gU8gCqMDDfw
==$0 8
------------------------------ ------------ ---------- ---------- ---------- -------------------
SYSAUX 94.44 510 28.38 481.63 2017-08-07 17:19:30
SYSTEM 93.24 800 54.06 745.94 2017-08-07 17:19:30
USERS 26.25 5 3.69 1.31 2017-08-07 17:19:30
UNDOTBS1 5.73 470 443.06 26.94 2017-08-07 17:19:30
TEST .05 20480 20470 10 2017-08-07 17:19:30
|
从上面可以看出，该表的大小变成了16M，而blocks也有1024个；表未被使用的空间已经释放，而且表空间可用大小也已经变大：
本文转自一个笨小孩51CTO博客，原文链接：http://blog.51cto.com/fengfeng688/1955372 ，如需转载请自行联系原作者
