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 來看囉~~

坦白說, 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: estimate_only,expdp,lob securefile