欧美一级特黄大片做受成人-亚洲成人一区二区电影-激情熟女一区二区三区-日韩专区欧美专区国产专区

shrink怎樣回收分區(qū)表碎片

shrink怎樣回收分區(qū)表碎片，針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

創(chuàng)新互聯(lián)建站服務(wù)項(xiàng)目包括冷水灘網(wǎng)站建設(shè)、冷水灘網(wǎng)站制作、冷水灘網(wǎng)頁(yè)制作以及冷水灘網(wǎng)絡(luò)營(yíng)銷(xiāo)策劃等。多年來(lái)，我們專(zhuān)注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，冷水灘網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶(hù)以成都為中心已經(jīng)輻射到冷水灘省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶(hù)的支持與信任！

實(shí)驗(yàn)如下：

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用戶(hù)執(zhí)行查詢(xún)表大小及碎片
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

結(jié)果同上。

--查詢(xún)每個(gè)分區(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>



--進(jìn)行shrink表t4（整個(gè)分區(qū)shrink，當(dāng)然也可以針對(duì)子分區(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)計(jì)信息：
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

--同時(shí)查看各子分區(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

關(guān)于shrink怎樣回收分區(qū)表碎片問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開(kāi)，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關(guān)知識(shí)。

網(wǎng)站欄目：shrink怎樣回收分區(qū)表碎片
標(biāo)題URL：http://aaarwkj.com/article38/gpgppp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供域名注冊(cè)、微信小程序、自適應(yīng)網(wǎng)站、建站公司、響應(yīng)式網(wǎng)站、云服務(wù)器

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

綿陽(yáng)服務(wù)器托管
性知音国产精品粉色视频| 久久日韩制服丝袜人妻| 日韩av在线黄色免费大全| 中文字慕日韩精品欧美一区| 欧美日韩三级性生活水平| 日韩精品一区高清视频| 久草国产免费福利在线视频| 怡红院怡春院视频免费看| 日韩欧美精品在线观看免费| 侵犯人妻中文字幕一区二区| 亚洲免费一区二区三区四区| 色哟哟哟哟免费观看视频 | 人妖激情一区二区三区| 日韩在线视频观看一区二区三区 | 国产av一区二区三区野战| 日本欧美一区二区三区高清| 97超碰97资源在线| 91九色视频免费观看| 亚洲免费av第一区第二区| 欧美美女福利午夜视频| 欧美日韩欧美国产精品| 国产91久久精品一区二区| 秒播视频午夜福利在线观看| 91精品国产综合久久香蕉麻豆| 亚洲一区日韩精品颜射| 精品成人在线一区二区| 国产一区二区三区婷婷 | 国产一区二区三区在线视频播放| 国产操大奶头女人自拍av | 国产av一区二区三区久久| 日本在线观看免费高清| 亚洲女人下体毛茸茸视频| 免费日本高清色噜噜视频| 亚洲va久久久噜噜噜久久狠| 日韩精品中文字幕免费人妻| 亚洲一区日本一区二区| 日本av天堂中文字幕| 亚洲精品日韩国产av| 人妻中字幕出轨中文字幕| 日韩精品一区二区三区高清 | 久久re这里只有精品6|