首页 / ORACLE / Oracle—deallocate unused释放高水位空间（二）

Oracle—deallocate unused释放高水位空间（二）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle—deallocate unused释放高水位空间（二），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9883字，纯文字阅读大概需要15分钟。

内容图文


使用说明和方法，官方文档有说明，如下：

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



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

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

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


⑦：验证空间是否被释放：

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个；表未被使用的空间已经释放，而且表空间可用大小也已经变大：


本文出自 “笨小孩的dba之路” 博客，请务必保留此出处http://fengfeng688.blog.51cto.com/4896812/1955372

Oracle—deallocate unused释放高水位空间（二）

标签：deallocate unused   oracle释放高水位空间   hwm高水位   

本文系统来源：http://fengfeng688.blog.51cto.com/4896812/1955372

内容总结

以上是互联网集市为您收集整理的Oracle—deallocate unused释放高水位空间（二）全部内容，希望文章能够帮你解决Oracle—deallocate unused释放高水位空间（二）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/488374.html

来源：【匿名】