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
|