shrink怎樣回收分區(qū)表碎片
shrink怎樣回收分區(qū)表碎片，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。
創(chuàng)新互聯(lián)建站服務項目包括冷水灘網(wǎng)站建設、冷水灘網(wǎng)站制作、冷水灘網(wǎng)頁制作以及冷水灘網(wǎng)絡營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關系等，向廣大中小型企業(yè)、政府機構等提供互聯(lián)網(wǎng)行業(yè)的解決方案，冷水灘網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟效益。目前，我們服務的客戶以成都為中心已經(jīng)輻射到冷水灘省份的部分城市，未來相信會繼續(xù)擴大服務區(qū)域并繼續(xù)獲得客戶的支持與信任！
實驗如下：
CREATE TABLE "SCOTT"."T4"
( "A" NUMBER,
"B" NUMBER
)
PARTITION BY RANGE ("A")
(PARTITION "PART1" VALUES LESS THAN (10),
PARTITION "PART2" VALUES LESS THAN (20) ) ;
begin
for v1 in 1..19
loop
insert into scott.t4 values(v1,dbms_random.value(1,100000));
commit;
end loop;
end;
/
INSERT INTO scott.T4 SELECT * FROM sT4;
SQL> CREATE TABLE "SCOTT"."T4"
( "A" NUMBER,
"B" NUMBER
2 3 4 )
5 PARTITION BY RANGE ("A")
6 (PARTITION "PART1" VALUES LESS THAN (10),
7 PARTITION "PART2" VALUES LESS THAN (20) ) ;
Table created.
SQL> begin
2 for v1 in 1..19
3 loop
4 insert into scott.t4 values(v1,dbms_random.value(1,100000));
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> conn scott/tiger;
Connected.
SQL> INSERT INTO T4 SELECT * FROM T4;
19 rows created.
省略.........
SQL> INSERT INTO T4 SELECT * FROM T4;
77824 rows created.
SQL> INSERT INTO T4 SELECT * FROM T4;
155648 rows created.
SQL> INSERT INTO T4 SELECT * FROM T4;
311296 rows created.
SQL> commit;
Commit complete.
SQL> commit;
Commit complete.
SQL> select count(*) from t4;
COUNT(*)
----------
622592
SQL>
--sys用戶執(zhí)行查詢表大小及碎片
SQL> col SEGMENT_NAME for a15
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT T4 PART1 TABLE PARTITION 16
SCOTT T4 PART2 TABLE PARTITION 16
SQL>
SQL> set lines 200
SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables
7 where table_name = '&table_name' and owner='&owner';
Enter value for table_name: T4
Enter value for owner: SCOTT
old 7: where table_name = '&table_name' and owner='&owner'
new 7: where table_name = 'T4' and owner='SCOTT'
TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4
SQL>
--分析表：
SQL> analyze table scott.T4 compute statistics;
Table analyzed.
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT T4 PART1 TABLE PARTITION 16
SCOTT T4 PART2 TABLE PARTITION 16
SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name='T4';
TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4 622592 16.625 31.46875 14.84375
SQL>
--刪除表數(shù)據(jù)：
SQL> delete scott.t4 where rownum < 600000;
599999 rows deleted.
SQL>
--再查看表發(fā)小及碎片情況：
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT T4 PART1 TABLE PARTITION 16
SCOTT T4 PART2 TABLE PARTITION 16
SQL>
SQL>
SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name='T4';
TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4 622592 16.625 31.46875 14.84375
SQL>
--再次分析一下表
SQL> analyze table scott.T4 compute statistics;
Table analyzed.
SQL>
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT T4 PART1 TABLE PARTITION 16
SCOTT T4 PART2 TABLE PARTITION 16
SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name='T4';
TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4 22593 .603298187 31.46875 30.8654518
SQL>
發(fā)現(xiàn)使勁的表大小已經(jīng)變?yōu)?.603298187MB了.
--使用dbms_stat包分析分區(qū)表，如下：
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SCOTT',TABNAME=> 'T4',METHOD_OPT=> 'for all indexed columns size auto',CASCADE=> TRUE,ESTIMATE_PERCENT=> 100);
PL/SQL procedure successfully completed.
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT T4 PART1 TABLE PARTITION 16
SCOTT T4 PART2 TABLE PARTITION 16
SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name='T4';
TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4 22593 .538659096 31.46875 30.9300909
結果同上。
--查詢每個分區(qū)的碎片情況；
SQL> select t3.table_name, t3.partition_name,t3.num_rows * t3.AVG_ROW_LEN / 1024 / 1024 actual_mb,T3.blocks * 8 / 1024 high_water_mb,(blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 ) need_recovery_mb,t3.empty_blocks,t3.last_analyzed from dba_tab_partitions t3 where t3.table_name ='T4';
TABLE_NAME PARTITION_NAME ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB EMPTY_BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ------------- ---------------- ------------ -------------------
T4 PART1 0 15.734375 15.734375 34 2017-10-18 06:06:49
T4 PART2 .538659096 15.734375 15.1957159 34 2017-10-18 06:06:49
SQL>
--進行shrink表t4（整個分區(qū)shrink，當然也可以針對子分區(qū)shrink,例如alter table PTABLE MODIFY PARTITION PTABLE_P2 shrink space）
SQL> alter table scott.t4 enable row movement;
Table altered.
SQL>
SQL> alter table scott.t4 shrink space cascade;
Table altered.
SQL> alter table scott.t4 disable row movement;
Table altered.
--查看表大小及碎片情況：
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT T4 PART1 TABLE PARTITION .1875
SCOTT T4 PART2 TABLE PARTITION .8125
SQL>
SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name='T4';
TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4 22593 .538659096 31.46875 30.9300909
SQL>
--再次收集統(tǒng)計信息：
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SCOTT',TABNAME=> 'T4',METHOD_OPT=> 'for all indexed columns size auto',CASCADE=> TRUE,ESTIMATE_PERCENT=> 100);
PL/SQL procedure successfully completed.
SQL>
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='T4';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_M
------------------------------ --------------- ------------------------------ ------------------ ----------
SCOTT T4 PART1 TABLE PARTITION .1875
SCOTT T4 PART2 TABLE PARTITION .8125
SQL> select table_name,
2 num_rows,
3 avg_row_len * num_rows / 1024 / 1024 actual_mb,
4 blocks * 8 / 1024 high_water_mb,
5 (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024) need_recovery_mb
6 from dba_tables where table_name='T4';
TABLE_NAME NUM_ROWS ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
------------------------------ ---------- ---------- ------------- ----------------
T4 22593 .538659096 .6640625 .125403404
--同時查看各子分區(qū)情況：
SQL> select t3.table_name, t3.partition_name,t3.num_rows * t3.AVG_ROW_LEN / 1024 / 1024 actual_mb,T3.blocks * 8 / 1024 high_water_mb,(blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 ) need_recovery_mb,t3.empty_blocks,t3.last_analyzed from dba_tab_partitions t3 where t3.table_name ='T4';
TABLE_NAME PARTITION_NAME ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB EMPTY_BLOCKS LAST_ANALYZED
------------------------------ ------------------------------ ---------- ------------- ---------------- ------------ -------------------
T4 PART1 0 .0078125 .0078125 34 2017-10-18 06:15:37
T4 PART2 .538659096 .65625 .117590904 34 2017-10-18 06:15:37
關于shrink怎樣回收分區(qū)表碎片問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關知識。
網(wǎng)站欄目：shrink怎樣回收分區(qū)表碎片
標題URL：http://muchs.cn/article38/gpgppp.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供域名注冊、微信小程序、自適應網(wǎng)站、建站公司、響應式網(wǎng)站、云服務器
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)
- 創(chuàng)新SEO：谷歌google搜索引擎優(yōu)化用戶模式 2016-05-24
- 淺談Google Play ASO 優(yōu)化 2020-09-10
- Google優(yōu)化內(nèi)容質(zhì)量和平臺規(guī)則哪個重要？ 2015-01-12
- Google關鍵詞排名優(yōu)化的6個技巧 2016-02-28
- 如何在Google Chrome中停用混合內(nèi)容安全警告 2016-09-19
- Google Ads主要有哪些推廣形式？ 2023-05-05
- 【SEO優(yōu)化公司】google網(wǎng)站SEO優(yōu)化入門技巧 2016-11-14
- 網(wǎng)站登陸Google的關鍵：網(wǎng)站外部鏈接 2016-08-08
- 谷歌相關鏈接Google Related Links內(nèi)測 2017-02-26
- google seo關鍵字推廣操作技巧？ 2016-04-16
- 提高Google Adsense廣告相關度的技巧 2017-02-26
- Google推廣需要明確的三個方向 2016-03-10