【TABLESPACE】怎麼去降低資料檔案的高水位呢(BLOCK_ID)

xysoul_雲龍發表於2017-08-11

http://blog.itpub.net/29487349/viewspace-2143418/ 這邊文章說了怎麼收縮表空間,那麼有的時候會出現以下情況,也就是我們查詢表空間空間空閒很多,但執行該文章中查詢可以resize的空間卻很小,這是為什麼呢? 好,下面我們來看一看。

例項,Oracle11.2.0.4 for redhat6.7 x64

查詢表空間使用狀況


點選(此處)摺疊或開啟

  1. TABLESPACE_NAME TOTAL FREE % Free
  2. ------------------------- ---------- ---------- ----------
  3. SYSAUX 520 27 5
  4. SYSTEM 750 9 1
  5. TEST 200 111 56 --- TEST表空間剩餘111M(測試,使用資料較小)
  6. UNDOTBS1 75 1 1
  7. USERS 76.25 3 4

執行 查詢可以resize的大小


點選(此處)摺疊或開啟

  1. Smallest
  2.                                                        Size Current Poss.
  3. FILE_NAME Poss. Size Savings
  4. -------------------------------------------------- -------- -------- --------
  5. /backup/oradata/mytest/test01.dbf 161 200 39
  6.                                                                      --------
  7. sum 39  也就是該資料檔案可以釋放39M,那麼這什麼怎麼回事呢

我們透過以下語句看看:


點選(此處)摺疊或開啟

  1. SQL> select owner ownr, segment_name name,PARTITION_NAME, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks
  2.   2 from dba_extents
  3.   3 where tablespace_name ='TEST'
  4.   4 order by block_id
  5.   5 /
  6. ………………………………

  7. SCOTT OBJPART_ID INDEX 21 5 4352 128
  8. SCOTT OBJPART_ID INDEX 22 5 4480 128
  9. SCOTT OBJPART_ID INDEX 23 5 4608 128
  10. SCOTT OBJPART_ID INDEX 24 5 4736 128
  11. SCOTT OBJPART_ID INDEX 25 5 4864 128
  12. SCOTT OBJPART_ID INDEX 26 5 5120 128
  13. SCOTT OBJ_PART P20141231 TABLE PA 0 5 9600 1,024
  14. SCOTT OBJ_PART P20141231 TABLE PA 1 5 10624 1,024
  15. SCOTT OBJ_PART P20141231 TABLE PA 2 5 19584 432      --查出 該分割槽表block_id比較大,也就是佔據資料檔案的位置最尾端(相對其他資料塊))

那麼我們可以透過move table方式降低該分割槽表的位置,也就移動到考前點的位置,此處請注意關於分割槽表相關的表、索引情況,請根據業務、資料庫來做判斷

執行下面命令:

點選(此處)摺疊或開啟

  1. SQL> alter table scott.obj_part MODIFY PARTITION p20141231 shrink space;  該命令並沒有降低資料檔案中block_id大小,字面來理解,它只是負責收縮,降低該分割槽表的高水位。  該命令無需重建索引

  2. Table altered.



  3. SQL> alter table scott.obj_part move partition p20141231 tablespace test;  --move 移至,移到表空間最前面的空閒block中,需要重建索引

  4. Table altered.

再次檢視錶空間resize情況

點選(此處)摺疊或開啟

  1. SCOTT OBJPART_ID INDEX 23 5 4608 128
  2. SCOTT OBJPART_ID INDEX 24 5 4736 128
  3. SCOTT OBJPART_ID INDEX 25 5 4864 128
  4. SCOTT OBJPART_ID INDEX 26 5 5120 128
  5. SCOTT OBJ_PART P20141231 TABLE PA 0 5 5248 1,024
  6. SCOTT OBJ_PART P20141231 TABLE PA 1 5 6272 1,024
  7. SCOTT OBJ_PART P20141231 TABLE PA 2 5 7296 1,024  --位置已前移


  8. SQL> select file_name,
  9.   2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
  10.   3 ceil( blocks*&&blksize/1024/1024) currsize,
  11.   4 ceil( blocks*&&blksize/1024/1024) -
  12.   5 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
  13.   6 from dba_data_files a,
  14.   7 ( select file_id, max(block_id+blocks-1) hwm
  15.   8 from dba_extents where tablespace_name='TEST'
  16.   9 group by file_id ) b
  17.  10 where a.file_id = b.file_id(+) and tablespace_name='TEST';

  18.                                                    Smallest
  19.                                                        Size Current Poss.
  20. FILE_NAME Poss. Size Savings
  21. -------------------------------------------------- -------- -------- --------
  22. /backup/oradata/mytest/test01.dbf 65 200 135
  23.                                                                      --------
  24. sum 135  --剩餘空間變大

再次執行相關alter …… resize操作即可。  當執行表、索引相關move、rebuild時,請了解業務及相關結構,任何操作都有風險,注意備份及操作規範。



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29487349/viewspace-2143421/,如需轉載,請註明出處,否則將追究法律責任。

相關文章