Oracle deallocate unused释放高水位空间

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;来释放未使用的空间

二、开始测试

①:查看测试环境表空间使用情况:

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

SQL> show user
USER is "ADMIN"    ----测试用的用户

②:创建测试分区表:test_emp 

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的空间:

SQL> alter table test_EMP  modify PARTITION pt_1 ALLOCATE EXTENT (size 10240m);
Table altered.

④:收集该分区表的统计信息,并查看该表的block块占用情况

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个;

⑤:向分区表插入一些数据,并查看该表的大小

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/29a0be0c0a2b6e125c8f055089cfef92.html