系統持續在清檔,但表格使用空間卻不斷地成長
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的空間
那該表格整體空間到底佔了多少?
坦白說, 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;
/
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;
/
最後大家看了這數字
可以撐得住一年
就“按兵不動”囉!