Oracle—deallocateunused釋放高水位空間（二）
deallocate unused :僅適用于釋放HWM高水位以上的空間，而無法釋放高水位以下的空間；比如對表預(yù)分配的空間
創(chuàng)新互聯(lián)公司主要從事網(wǎng)頁設(shè)計、PC網(wǎng)站建設(shè)（電腦版網(wǎng)站建設(shè)）、wap網(wǎng)站建設(shè)（手機(jī)版網(wǎng)站建設(shè)）、成都響應(yīng)式網(wǎng)站建設(shè)公司、程序開發(fā)、網(wǎng)站優(yōu)化、微網(wǎng)站、小程序定制開發(fā)等，憑借多年來在互聯(lián)網(wǎng)的打拼，我們在互聯(lián)網(wǎng)網(wǎng)站建設(shè)行業(yè)積累了豐富的做網(wǎng)站、成都做網(wǎng)站、網(wǎng)站設(shè)計、網(wǎng)絡(luò)營銷經(jīng)驗，集策劃、開發(fā)、設(shè)計、營銷、管理等多方位專業(yè)化運作于一體。
使用說明和方法，官方文檔有說明，如下：
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)
一、測試環(huán)境：
Oracle11.2.0.4
使用admin用戶在test表空間新建一張測試分區(qū)表，并提前對子分區(qū)預(yù)分配一些空間，然后插入少量的數(shù)據(jù)，最后使用：alter table .....deallocate unused;來釋放未使用的空間
二、開始測試
①：查看測試環(huán)境表空間使用情況：
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 hh34: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" ----測試用的用戶
②：創(chuàng)建測試分區(qū)表：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分區(qū)表預(yù)分配10G的空間：
SQL> alter table test_EMP modify PARTITION pt_1 ALLOCATE EXTENT (size 10240m); Table altered.
④：收集該分區(qū)表的統(tǒng)計信息，并查看該表的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分區(qū)表的大小是10G，而占用的blocks有1315840個；
⑤：向分區(qū)表插入一些數(shù)據(jù)，并查看該表的大小
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; ---執(zhí)行存儲過程插入數(shù)據(jù) PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats(ownname =>'ADMIN',tabname =>'TEST_EMP',cascade=>TRUE); ---收集該表的統(tǒng)計信息 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事件，在實際操作中只需執(zhí)行：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; ---執(zhí)行該命令釋放子分區(qū)未被使用的空間； Table altered. SQL> alter session set events '10046 trace name context off'; ---關(guān)閉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個；表未被使用的空間已經(jīng)釋放，而且表空間可用大小也已經(jīng)變大：
名稱欄目：Oracle—deallocateunused釋放高水位空間（二）
文章網(wǎng)址：http://aaarwkj.com/article26/jeejcg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計、小程序開發(fā)、自適應(yīng)網(wǎng)站、云服務(wù)器、做網(wǎng)站、外貿(mào)建站
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 淺談實體店鋪開發(fā)微信小程序的重大意義 2016-10-23
- 對話微信小程序負(fù)責(zé)人：保持克制，擔(dān)心市場被吹大 2022-02-22
- 便利店微信小程序的引流方法有哪些？ 2014-02-05
- 微信小程序運營的三種目的、三點思考！ 2022-05-20
- 實體店開發(fā)微信小程序的必要性 2022-11-19
- 小程序開發(fā)公司：微信小程序應(yīng)該怎樣引流變現(xiàn) 2023-03-21
- 百度智能小程序與微信小程序有什么不一樣？一張圖告訴你真像！ 2014-08-07
- 微信小程序button組件運用圖片作為背景 2023-03-04
- 微信小程序開發(fā)大概需要多少錢？ 2020-12-04
- 微信小程序開發(fā)要注意什么？ 2022-06-10
- 使用微信小程序的好處有哪些 2021-02-25
- 上海電商微信小程序如何開發(fā)？ 2021-01-04