SET SERVEROUTPUT ON
SET LINESIZE 300
SET PAGESIZE 1000
DECLARE
v_ts_name VARCHAR2(30) := 'YOUR_TABLESPACE_NAME'; -- << CHANGE
v_block_size NUMBER;
v_buffer_mb CONSTANT NUMBER := 10;
v_show_shrink_cmds BOOLEAN := FALSE; -- Toggle to TRUE if needed
BEGIN
-- Get block size once
SELECT block_size INTO v_block_size
FROM dba_tablespaces
WHERE tablespace_name = UPPER(v_ts_name);
DBMS_OUTPUT.PUT_LINE('===== ANALYSIS FOR TABLESPACE: ' || v_ts_name || ' =====');
-- Combined HWM + datafile info
FOR df IN (
SELECT
df.file_id,
df.file_name,
df.bytes AS file_bytes,
NVL(MAX(e.block_id + e.blocks), 0) AS hwm_block
FROM
dba_data_files df
LEFT JOIN dba_extents e ON df.file_id = e.file_id
WHERE
df.tablespace_name = UPPER(v_ts_name)
GROUP BY
df.file_id, df.file_name, df.bytes
) LOOP
DECLARE
v_hwm_bytes NUMBER := df.hwm_block * v_block_size;
v_resize_target_mb NUMBER := CEIL((df.hwm_block * v_block_size + v_buffer_mb * 1024 * 1024) / 1024 / 1024);
BEGIN
DBMS_OUTPUT.PUT_LINE(CHR(10) || '>> File: ' || df.file_name);
DBMS_OUTPUT.PUT_LINE(' Current Size: ' || ROUND(df.file_bytes / 1024 / 1024) || ' MB');
DBMS_OUTPUT.PUT_LINE(' HWM Estimate: ' || ROUND(v_hwm_bytes / 1024 / 1024) || ' MB');
DBMS_OUTPUT.PUT_LINE(' Safe Resize To: ' || v_resize_target_mb || ' MB');
IF df.file_bytes > (v_hwm_bytes + v_buffer_mb * 1024 * 1024) THEN
DBMS_OUTPUT.PUT_LINE(' --> SUGGEST: ALTER DATABASE DATAFILE ''' || df.file_name || ''' RESIZE ' || v_resize_target_mb || 'M;');
ELSE
DBMS_OUTPUT.PUT_LINE(' --> No reclaimable space found.');
END IF;
-- Show top 3 segments near the end of file
DBMS_OUTPUT.PUT_LINE(' Segments near file end (may block resize):');
FOR seg IN (
SELECT *
FROM (
SELECT owner, segment_name, segment_type,
(block_id + blocks) * v_block_size / 1024 / 1024 AS segment_end_mb
FROM dba_extents
WHERE file_id = df.file_id
ORDER BY segment_end_mb DESC
)
WHERE ROWNUM <= 3
) LOOP
DBMS_OUTPUT.PUT_LINE(' - ' || seg.segment_type || ' ' || seg.owner || '.' || seg.segment_name || ' ends at ~' || ROUND(seg.segment_end_mb) || ' MB');
END LOOP;
END;
END LOOP;
-- Shrink recommendations if toggled
IF v_show_shrink_cmds THEN
DBMS_OUTPUT.PUT_LINE(CHR(10) || '===== SHRINK CANDIDATES =====');
FOR tbl IN (
SELECT owner, segment_name
FROM dba_segments
WHERE segment_type = 'TABLE'
AND tablespace_name = UPPER(v_ts_name)
) LOOP
DBMS_OUTPUT.PUT_LINE(
'ALTER TABLE ' || tbl.owner || '.' || tbl.segment_name || ' ENABLE ROW MOVEMENT;'
);
DBMS_OUTPUT.PUT_LINE(
'ALTER TABLE ' || tbl.owner || '.' || tbl.segment_name || ' SHRINK SPACE;'
);
END LOOP;
FOR idx IN (
SELECT owner, segment_name
FROM dba_segments
WHERE segment_type = 'INDEX'
AND tablespace_name = UPPER(v_ts_name)
) LOOP
DBMS_OUTPUT.PUT_LINE(
'ALTER INDEX ' || idx.owner || '.' || idx.segment_name || ' SHRINK SPACE;'
);
END LOOP;
END IF;
END;
/
0 comments:
Post a Comment