✅ What This Script Does
-
Analyzes each datafile in a given tablespace.
-
Calculates a safe resize size based on HWM + buffer.
-
Lists any segments blocking resize (i.e., those near the file end).
-
Generates shrink commands for tables and indexes.
-
Ensures you're prepared to safely resize without hitting ORA-03297.
SET SERVEROUTPUT ON
SET LINESIZE 300
SET PAGESIZE 1000
DECLARE
v_ts_name VARCHAR2(30) := 'YOUR_TABLESPACE_NAME'; -- << Change here
v_block_size NUMBER;
v_hwm_blocks NUMBER;
v_hwm_bytes NUMBER;
v_current_bytes NUMBER;
v_file_id NUMBER;
v_file_name VARCHAR2(512);
v_buffer_mb CONSTANT NUMBER := 10; -- Safety buffer
v_resize_target_mb NUMBER;
v_owner VARCHAR2(30);
v_segment_name VARCHAR2(30);
v_segment_type VARCHAR2(20);
BEGIN
-- Get block size
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 || ' =====');
-- Loop through each datafile
FOR df IN (
SELECT file_id, file_name, bytes
FROM dba_data_files
WHERE tablespace_name = UPPER(v_ts_name)
) LOOP
v_file_id := df.file_id;
v_file_name := df.file_name;
v_current_bytes := df.bytes;
-- Get High Water Mark
SELECT NVL(MAX(block_id + blocks), 0)
INTO v_hwm_blocks
FROM dba_extents
WHERE file_id = v_file_id;
v_hwm_bytes := v_hwm_blocks * v_block_size;
v_resize_target_mb := CEIL((v_hwm_bytes + v_buffer_mb * 1024 * 1024) / 1024 / 1024);
DBMS_OUTPUT.PUT_LINE(CHR(10) || '>> File: ' || v_file_name);
DBMS_OUTPUT.PUT_LINE(' Current Size: ' || ROUND(v_current_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 v_current_bytes > (v_hwm_bytes + v_buffer_mb * 1024 * 1024) THEN
DBMS_OUTPUT.PUT_LINE(' --> SUGGEST: ALTER DATABASE DATAFILE ''' || v_file_name || ''' RESIZE ' || v_resize_target_mb || 'M;');
ELSE
DBMS_OUTPUT.PUT_LINE(' --> No reclaimable space found.');
END IF;
-- Show segments at tail end of file
DBMS_OUTPUT.PUT_LINE(' Segments near end of file (may block resize):');
FOR seg IN (
SELECT owner, segment_name, segment_type,
(block_id + blocks) * v_block_size / 1024 / 1024 AS segment_end_mb
FROM dba_extents
WHERE file_id = v_file_id
AND (block_id + blocks) * v_block_size / 1024 > (v_current_bytes - 20 * 1024 * 1024) -- within last 20MB
ORDER BY segment_end_mb DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(' - ' || seg.segment_type || ' ' || seg.owner || '.' || seg.segment_name ||
' ends at ~' || ROUND(seg.segment_end_mb) || ' MB');
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || '===== SHRINK CANDIDATES =====');
-- Generate shrink space statements
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;
/
0 comments:
Post a Comment