ORACLE表領域の未使用領域と空き領域最高水位標(HWM)取得方法 (セグメント領域情報　エクステントID　使用ブロック数)

SET SERVEROUTPUT ON SIZE 50000 feedback off pagesize 0 trims on lines 1000
spool SEGMENT_DATA.TXT

declare
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;

V_UNFORMATTED_BLOCKS NUMBER; --未フォーマットのブロックの合計数。
V_UNFORMATTED_BYTES NUMBER; --未フォーマットのバイトの合計数

V_FS1_BLOCKS NUMBER; --空き領域が最低で0から25%のブロック数
V_FS1_BYTES NUMBER; --空き領域が最低で0から25%のバイト数
V_FS2_BLOCKS NUMBER; --空き領域が最低で25から50%のブロック数
V_FS2_BYTES NUMBER; --空き領域が最低で25から50%のバイト数
V_FS3_BLOCKS NUMBER; --空き領域が最低で50から75%のブロック数
V_FS3_BYTES NUMBER; --空き領域が最低で50から75%のバイト数
V_FS4_BLOCKS NUMBER; --空き領域が最低で75から100%のブロック数
V_FS4_BYTES NUMBER; --空き領域が最低で75から100%のバイト数
V_FULL_BLOCKS NUMBER; --セグメントが一杯になった場合のブロックの合計数
V_FULL_BYTES NUMBER; --セグメントが一杯になった場合のバイトの合計数

V_FREE_KBYTES NUMBER;
V_FREE_BYTES NUMBER;
V_USED_BYTES NUMBER;

V_BYTES NUMBER;
V_KBYTES NUMBER;
V_EXTENTS NUMBER;

V_PERCENT NUMBER;
REC NUMBER(10);

V_OWNER VARCHAR2(30) := 'スキーマ';
V_TAB_NAME VARCHAR2(30);
V_TYPE VARCHAR2(30) := 'TABLE';
V_HWM_EXTENT NUMBER;
V_SYSDATE DATE;
/*=====================
|| テーブル名,バイト数取得
=======================*/
CURSOR SEGMENT_GET IS 
SELECT s.SEGMENT_NAME,s.BYTES,t.INITIAL_EXTENT,NUM_ROWS,EXTENTS FROM DBA_SEGMENTS s,dba_tables t
WHERE 
s.SEGMENT_TYPE = 'TABLE'
and t.OWNER = 'SCOTT' AND
and t.TABLESPACE_NAME = '表領域'
and t.TABLE_NAME = s.SEGMENT_NAME
ORDER BY s.SEGMENT_NAME;
DATA_SEGMENT_GET SEGMENT_GET%ROWTYPE;
begin
DBMS_OUTPUT.ENABLE(NULL);
dbms_output.put_line(
'テーブル名'
||','||'データ件数'
||','||'現セグメント容量(バイト)'
||','||'消費容量(バイト)'
||','||'空き容量(バイト)'
||','||'消費率(％)'
||','||'初期割り当てサイズ'
||','||'FULL_BYTES'
||','||'FULL_BLOCKS'
||','||'0 - 25% free blocks'
||','||'25- 50% free blocks'
||','||'50- 75% free blocks'
||','||'75-100% free blocks'
||','||'UNFORMATTED_BLOCKS'
||','||'UNFORMATTED_BYTES'
||','||'使用エクステント数'
||','||'HWMのあるエクステントＩＤ'
||','||'HWMのあるブロックの位置'
||','||'HWMのあるデータファイルのID'
||','||'HWMのあるエクステントの開始ブロックID'
||','||'TOTALブロック数'
||','||'使用ブロック数'
||','||'未使用ブロック数'
||','||'取得日'
);
OPEN SEGMENT_GET;
LOOP
FETCH SEGMENT_GET INTO DATA_SEGMENT_GET;
EXIT WHEN SEGMENT_GET%NOTFOUND OR DATA_SEGMENT_GET.SEGMENT_NAME IS NULL;

V_TAB_NAME := DATA_SEGMENT_GET.SEGMENT_NAME;
-- execute immediate 'select count(1) from ' || V_TAB_NAME into REC;

--HWM以降の未使用領域取得
dbms_space.unused_space(V_OWNER, V_TAB_NAME, V_TYPE,
v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes,
v_last_used_extent_file_id, v_last_used_extent_block_id,
v_last_used_block);

--セグメント領域取得
DBMS_SPACE.SPACE_USAGE(
SEGMENT_OWNER => V_OWNER,
SEGMENT_NAME => V_TAB_NAME,
SEGMENT_TYPE => V_TYPE,
UNFORMATTED_BLOCKS => V_UNFORMATTED_BLOCKS,
UNFORMATTED_BYTES => V_UNFORMATTED_BYTES,
FS1_BLOCKS => V_FS1_BLOCKS,
FS1_BYTES => V_FS1_BYTES,
FS2_BLOCKS => V_FS2_BLOCKS,
FS2_BYTES => V_FS2_BYTES,
FS3_BLOCKS => V_FS3_BLOCKS,
FS3_BYTES => V_FS3_BYTES,
FS4_BLOCKS => V_FS4_BLOCKS,
FS4_BYTES => V_FS4_BYTES,
FULL_BLOCKS => V_FULL_BLOCKS,
FULL_BYTES => V_FULL_BYTES
);
--HWM=TOTAL_BLOCKS-UNUSED_BLOCKS
--INITIAL_EXTENT
V_BYTES := DATA_SEGMENT_GET.BYTES;
V_KBYTES := ROUND(V_BYTES/1024,2);
V_FREE_BYTES := V_BYTES - V_FULL_BYTES - V_FS4_BYTES 
- V_FS3_BYTES - V_FS2_BYTES - V_FS1_BYTES * 0.25;
V_FREE_KBYTES := ROUND(V_FREE_BYTES/1024,2);
V_PERCENT := (V_BYTES - V_FREE_BYTES) / V_BYTES * 100;
V_USED_BYTES := V_BYTES - V_FREE_BYTES;
V_EXTENTS := DATA_SEGMENT_GET.EXTENTS;

-- dbms_output.put_line('テーブル名：'||V_TAB_NAME||'　||　HWMブロックＩＤ：'||v_last_used_extent_block_id);
--HWMのあるエクステントID取得
SELECT TO_CHAR(SYSDATE,'YYYYMMDD'),nvl(E.EXTENT_ID,0) INTO V_SYSDATE,V_HWM_EXTENT FROM DBA_SEGMENTS S,DBA_EXTENTS E
WHERE
S.TABLESPACE_NAME = '表領域'
AND S.SEGMENT_NAME = E.SEGMENT_NAME
AND S.SEGMENT_NAME = V_TAB_NAME
AND E.FILE_ID = v_last_used_extent_file_id
AND E.BLOCK_ID = v_last_used_extent_block_id;

dbms_output.put_line(
V_TAB_NAME
||','||DATA_SEGMENT_GET.NUM_ROWS
||','||to_char(V_BYTES, '999999999990')
||','||to_char(V_USED_BYTES, '999999999990')
||','||to_char(V_FREE_BYTES, '999999999990')
||','||to_char(V_PERCENT, '990.99')
||','||DATA_SEGMENT_GET.INITIAL_EXTENT
||','||V_FULL_BYTES
||','||V_FULL_BLOCKS
||','||V_FS1_BLOCKS
||','||V_FS2_BLOCKS
||','||V_FS3_BLOCKS
||','||V_FS4_BLOCKS
||','||V_UNFORMATTED_BLOCKS
||','||V_UNFORMATTED_BYTES
||','||V_EXTENTS
||','||V_HWM_EXTENT
||','||to_char(v_last_used_block, '9999990')
||','||to_char(v_last_used_extent_file_id, '9999990')
||','||to_char(v_last_used_extent_block_id, '9999990')
||','||to_char(v_total_blocks, '999999999990')
||','||to_char(v_total_blocks - v_unused_blocks, '999999999990')
||','||to_char(v_unused_blocks, '999999999990')
||','||V_SYSDATE
);
END LOOP;
CLOSE SEGMENT_GET;
end;
/
spool off