f Delete table with lob securefile causes fragmentation ~ 迪貝之家

Delete table with lob securefile causes fragmentation

系統持續在清檔,但表格使用空間卻不斷地成長

DBA能做的就是查找資料
透過google search 及查詢mtealink
至少看了六篇Oracle的技術文件
才有一個結論出來
底下是我跟AP Leader的互動
---------------------------------------------
    經研究下列文件,進行該表LOB物件所佔空間的分析
How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized? (Doc ID 1453350.1)
    跑出來的數據如下:
SQL> exec check_space_securefile('ADM','LOB_STORE_BCK_1');
Segment Blocks/Bytes   = 21510808 / 176216539136
Unused Blocks/Bytes    = 39917 / 327000064
Used Blocks/Bytes      = 14816995 / 121380823040
Expired Blocks/Bytes   = 6653896 / 54508716032
Unexpired Blocks/Bytes = 0 / 0
===========================================================================
NON Data Blocks/Bytes  = 6693813 / 54835716096

PL/SQL procedure successfully completed.
文件指出該如何進行判斷物件是否有破碎空間
3) Determine if the LOBSEGMENT is a candidate for shrink / reorganization

The following query will show the extents allocated for the LOBSEGMENT

SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'LOB_STORE_BCK_1' GROUP BY BYTES ORDER BY 2;

If NON Data Bytes is greater than one or more of the extent sizes from this query ...
     then this segment is a candiate for a shrink / reorganization as this process will likely be able to reduce the size of the LOBSEGMENT by at least one extent
query 的output :
     BYTES   COUNT(*)
---------- ----------
  47185920          1
    131072          1
     65536          1
  62914560          7
   1048576          8
  63963136         28
  67108864       2110
   8388608       3854

我的判斷是, fragmentaion大概浪費了約50GB的空間
那該表格整體空間到底佔了多少?
AP Leader說他們的語法抓出來是70幾GB
所以我用expdp estimate_only 來看囉~~
ESTIMATE_ONLY

坦白說, Oracle DBA如果沒有metalink
就等於老虎沒了牙

CREATE OR REPLACE PROCEDURE check_space_securefile (u_name in varchar2, v_segname varchar2 ) IS
     l_segment_size_blocks NUMBER;
     l_segment_size_bytes NUMBER;
     l_used_blocks NUMBER;
     l_used_bytes NUMBER;
     l_expired_blocks NUMBER;
     l_expired_bytes NUMBER;
     l_unexpired_blocks NUMBER;
     l_unexpired_bytes NUMBER;
     l_unused_blocks NUMBER;
     l_unused_bytes NUMBER;
     l_non_data_blocks NUMBER;
     l_non_data_bytes NUMBER;

      BEGIN
          DBMS_SPACE.SPACE_USAGE( segment_owner =>u_name,
                                  segment_name => v_segname,
                                  segment_type => 'LOB',
                                  segment_size_blocks => l_segment_size_blocks,
                                  segment_size_bytes => l_segment_size_bytes,
                                  used_blocks => l_used_blocks,
                                  used_bytes => l_used_bytes,
                                  expired_blocks => l_expired_blocks,
                                  expired_bytes => l_expired_bytes,
                                  unexpired_blocks => l_unexpired_blocks,
                                  unexpired_bytes => l_unexpired_bytes
           );

           l_unused_blocks := l_segment_size_blocks - (l_used_blocks + l_expired_blocks + l_unexpired_blocks);
          l_unused_bytes := l_segment_size_bytes - (l_used_bytes + l_expired_bytes + l_unexpired_bytes);

           l_non_data_blocks := l_unused_blocks + l_expired_blocks + l_unexpired_blocks;
          l_non_data_bytes :=  l_unused_bytes + l_expired_bytes + l_unexpired_bytes;

           DBMS_OUTPUT.ENABLE;
          DBMS_OUTPUT.PUT_LINE(' Segment Blocks/Bytes   = '||l_segment_size_blocks||' / '||l_segment_size_bytes);
          DBMS_OUTPUT.PUT_LINE(' Unused Blocks/Bytes    = '||l_unused_blocks||' / '||l_unused_bytes);
          DBMS_OUTPUT.PUT_LINE(' Used Blocks/Bytes      = '||l_used_blocks||' / '||l_used_bytes);
          DBMS_OUTPUT.PUT_LINE(' Expired Blocks/Bytes   = '||l_expired_blocks||' / '||l_expired_bytes);
          DBMS_OUTPUT.PUT_LINE(' Unexpired Blocks/Bytes = '||l_unexpired_blocks||' / '||l_unexpired_bytes);
          DBMS_OUTPUT.PUT_LINE('===========================================================================');
          DBMS_OUTPUT.PUT_LINE(' NON Data Blocks/Bytes  = '||l_non_data_blocks||' / '||l_non_data_bytes);
     END;

/
最後大家看了這數字
可以撐得住一年
就“按兵不動”囉！




Posted in: ,,