ere’s a robust Oracle script that:
✅ Analyzes tables and indexes to reduce HWM
✅ Shrinks them if eligible
✅ Calculates the new possible minimum datafile size
✅ Dynamically generates the ALTER DATABASE DATAFILE ... RESIZE commands
-- Enable serveroutput
SET SERVEROUTPUT ON SIZE 1000000;
-- Cursor over all user tables
BEGIN
FOR t IN (
SELECT table_name
FROM user_tables
WHERE temporary = 'N'
) LOOP
BEGIN
-- Enable row movement
EXECUTE IMMEDIATE 'ALTER TABLE ' || t.table_name || ' ENABLE ROW MOVEMENT';
-- Shrink table space and reduce HWM
EXECUTE IMMEDIATE 'ALTER TABLE ' || t.table_name || ' SHRINK SPACE';
DBMS_OUTPUT.PUT_LINE('Shrunk table: ' || t.table_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Skipped table: ' || t.table_name || ' - ' || SQLERRM);
END;
END LOOP;
END;
/
-- Cursor over all user indexes
BEGIN
FOR i IN (
SELECT index_name
FROM user_indexes
WHERE temporary = 'N'
) LOOP
BEGIN
-- Shrink index space
EXECUTE IMMEDIATE 'ALTER INDEX ' || i.index_name || ' SHRINK SPACE';
DBMS_OUTPUT.PUT_LINE('Shrunk index: ' || i.index_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Skipped index: ' || i.index_name || ' - ' || SQLERRM);
END;
END LOOP;
END;
/
-- Analyze datafiles for potential resize
SET LINES 200
COL datafile_name FOR A60
DECLARE
v_sql VARCHAR2(1000);
BEGIN
FOR r IN (
SELECT d.file_id,
d.file_name AS datafile_name,
d.bytes / 1024 / 1024 AS current_size_mb,
f.maxbytes / 1024 / 1024 AS max_free_mb,
(d.bytes - f.maxbytes) / 1024 / 1024 AS suggested_resize_mb
FROM dba_data_files d
JOIN (
SELECT file_id, MAX(block_id + blocks) * (SELECT value FROM v$parameter WHERE name = 'db_block_size') AS maxbytes
FROM dba_free_space
GROUP BY file_id
) f ON d.file_id = f.file_id
WHERE d.autoextensible = 'NO' -- exclude autoextensible files for now
) LOOP
v_sql := 'ALTER DATABASE DATAFILE ''' || r.datafile_name || ''' RESIZE ' || ROUND(r.suggested_resize_mb) || 'M;';
DBMS_OUTPUT.PUT_LINE('-- Current: ' || ROUND(r.current_size_mb) || ' MB, Suggested Resize: ' || ROUND(r.suggested_resize_mb) || ' MB');
DBMS_OUTPUT.PUT_LINE(v_sql);
END LOOP;
END;
/
0 comments:
Post a Comment