[Oracle 11gR2] 데이터파일 크기를 줄이는 절차
테이블스페이스를 DROP하면 물리적인 데이터파일도 자동 삭제됩니다. 반면, 테이블이나 인덱스 DROP은 내부 데이터만 비울 뿐, 데이터파일이 차지하는 디스크 공간을 자동으로 반환하지는 않습니다. 오라클은 대량의 데이터가 삭제(DELETE)되더라도, 테이블의 최대 확장선인 HWM(High Water Mark)은 그대로 유지되기 때문에 해당 테이블에 한 번 늘려놓은 물리적 공간을 OS에 자동으로 반환하지 않습니다. 인덱스 역시 DELETE가 발생하면 밸런스가 깨지고 빈 공간이 대거 발생하여 구조가 비대해집니다.
따라서 데이터 파일 크기를 무작정 줄이려고(RESIZE) 하면 데이터가 없는데도 ORA-03297 (데이터 파일에 할당된 값 이상의 데이터가 있습니다) 에러를 만나게 됩니다. HWM이 파일 끝자락에 걸려있기 때문입니다. 이 문제를 해결하고 데이터파일 크기를 물리적으로 줄이려면 아래의 순서대로 작업을 진행해야 합니다.
- 테이블 조각 모음 및 HWM 초기화(TABLE MOVE)
- UNUSABLE 상태 복구 및 깨진 인덱스 구조 최적화(INDEX REBUILD)
- 오라클 옵티마이저에게 최신 상태 알림(DBMS_STATS)
- OS 디스크 공간 최종 반환(DATAFILE RESIZE)
datafile의 크기를 줄이기에 앞서 datafile크기에 대해 이해해 봅시다.
이 컬럼들을 묶어서 보면 세 가지 판단이 한 번에 됩니다. 확장 위험은 autoext와 headroom_mb(여력 소진 여부), 회수 가능성은 shrinkable_mb(당장 RESIZE 효과), 추가 정리 필요 여부는 used_mb와 hwm_mb의 격차(실사용은 적은데 HWM가 높으면 SHRINK/이전 검토)로 읽으시면 됩니다.
SELECT df.tablespace_name, df.file_id,
df.autoextensible AS autoext,
ROUND(df.bytes/1024/1024) AS cur_mb,
ROUND(df.maxbytes/1024/1024) AS max_mb,
ROUND((df.bytes - NVL(fs.free_b,0))/1024/1024) AS used_mb,
-- 데이터파일 HWM: 사용된 가장 높은 블록 위치 = RESIZE 하한선
CEIL(NVL(hwm.hwm_blocks,1) * ts.block_size /1024/1024) AS hwm_mb,
-- 실제 확장 한계: autoextend OFF 면 현재크기가 곧 천장
CASE WHEN df.autoextensible = 'YES'
THEN ROUND(df.maxbytes/1024/1024)
ELSE ROUND(df.bytes/1024/1024)
END AS effective_cap_mb,
-- 앞으로 더 확장 가능한 여력
CASE WHEN df.autoextensible = 'YES'
THEN ROUND((df.maxbytes - df.bytes)/1024/1024)
ELSE 0
END AS headroom_mb,
-- RESIZE 로 회수 가능한 여지 (cur - hwm)
GREATEST(ROUND(df.bytes/1024/1024)
- CEIL(NVL(hwm.hwm_blocks,1) * ts.block_size /1024/1024), 0) AS shrinkable_mb
FROM dba_data_files df
JOIN dba_tablespaces ts ON ts.tablespace_name = df.tablespace_name
LEFT JOIN (SELECT file_id, SUM(bytes) free_b
FROM dba_free_space GROUP BY file_id) fs
ON fs.file_id = df.file_id
LEFT JOIN (SELECT file_id, MAX(block_id + blocks - 1) AS hwm_blocks
FROM dba_extents GROUP BY file_id) hwm
ON hwm.file_id = df.file_id
ORDER BY shrinkable_mb DESC, df.tablespace_name, df.file_id;
- tablespace_name — 데이터파일이 속한 테이블스페이스 이름입니다. 같은 테이블스페이스에 여러 파일이 있을 수 있어, 파일별 행을 테이블스페이스 단위로 묶어 볼 때 기준이 됩니다.
- file_id — 데이터파일의 식별 번호(절대 파일번호)입니다. RESIZE나 파일 관련 작업에서 특정 파일을 지목할 때 쓰며, dba_data_files와 dba_extents를 연결하는 키이기도 합니다.
- autoext (autoextensible) — 이 파일의 자동 확장(autoextend) 설정으로 YES/NO 값입니다. YES면 공간이 부족할 때 파일이 maxbytes 한계까지 스스로 커지고, NO면 현재 크기에서 고정되어 더 늘어나지 않습니다. NO인 파일이 확장 실패(ORA-01653 등) 위험이 가장 큽니다.
- cur_mb (bytes) — 파일의 현재 물리적 크기(MB)입니다. OS상에서 이 파일이 실제로 차지하는 용량이며, 안에 데이터가 얼마나 들었는지와는 별개로 “지금 잡고 있는 크기”입니다.
- max_mb (maxbytes) — autoextend가 커질 수 있는 최대 크기(MB)입니다. 주의할 점은, autoextend가 OFF면 보통 0으로 나오고, autoextend가 ON이라도 MAXSIZE를 UNLIMITED로 두면 역시 0으로 표시된다는 것입니다. 그래서 이 값은 반드시 autoext와 함께 해석해야 합니다(0이라고 무조건 “무제한”이 아님).
- used_mb (bytes – free_b) — 파일 안에서 실제 데이터가 차지한 양(MB)입니다. 현재 크기에서 그 파일의 빈 공간(dba_free_space 합계)을 뺀 값으로, 진짜 사용량을 나타냅니다. cur_mb와의 차이가 클수록 파일 안에 빈 공간이 많다는 뜻입니다.
- hwm_mb (HWM 블록 × 블록크기) — 데이터파일의 high water mark, 즉 그 파일에서 데이터가 들어찬 가장 높은 블록 위치를 MB로 환산한 값입니다. dba_extents에서 파일별 최고 블록(MAX(block_id + blocks – 1))에 테이블스페이스 블록 크기를 곱해 구합니다. RESIZE는 이 값 아래로는 못 줄이므로(ORA-03297), 파일을 얼마나 줄일 수 있는지의 하한선입니다. used_mb는 작은데 hwm_mb가 크면, MOVE/REBUILD 반복으로 데이터가 파일 위쪽에 박혀 HWM가 비대해진 상태입니다.
- effective_cap_mb (실효 확장 한계) — 이 파일이 실제로 커질 수 있는 진짜 천장(MB)입니다. autoext = YES면 max_mb가 천장이고, autoext = NO면 더 못 늘어나므로 현재 크기(cur_mb)가 곧 천장이 됩니다. max_mb만 보면 놓치는 “고정 크기 파일의 한계”를 이 컬럼이 보정해 줍니다.
- headroom_mb (확장 여력) — 지금부터 앞으로 더 늘릴 수 있는 공간(MB)입니다. autoext = YES면 max_mb – cur_mb, autoext = NO면 0입니다. 이 값이 작거나 0인 파일은 해당 테이블스페이스의 가용 공간이 바닥나는 순간 확장 실패가 나므로 가장 먼저 점검해야 합니다. 쿼리에서 이 컬럼으로 위험도를 가늠합니다.
- shrinkable_mb (회수 가능량) — 지금 당장 RESIZE로 줄여서 되돌릴 수 있는 공간(MB)으로, cur_mb – hwm_mb입니다(음수면 0으로 처리). 결과가 이 값 기준 내림차순으로 정렬되어 있어, 회수 효과가 큰 파일부터 위로 올라옵니다. 이 값이 크면 단순 RESIZE만으로 공간을 많이 돌려받을 수 있고, 반대로 used_mb는 적은데 shrinkable_mb도 작다면 RESIZE로는 부족해 SHRINK SPACE나 다른 테이블스페이스로의 이전이 필요하다는 신호입니다.
먼저 아래 쿼리를 실행하여 TABLE MOVE 및 INDEX REBUILD 이전의 datafile 상태를 확인합니다.
SELECT F.TABLESPACE_NAME,
F.FILE_ID,
F.FILE_NAME,
F.BYTES / 1024 / 1024 AS CURRENT_MB,
NVL(E.USED_MB, 0) AS USED_MB,
NVL(E.MAX_MB, 0) AS HWM_MB,
(F.BYTES / 1024 / 1024) - NVL(E.MAX_MB, 0) AS SAVING_POSSIBLE_MB
FROM DBA_DATA_FILES F,
(SELECT FILE_ID,
ROUND(SUM(BYTES) / 1024 / 1024) AS USED_MB,
ROUND(MAX(BLOCK_ID + BLOCKS) * 8192 / 1024 / 1024) AS MAX_MB
FROM DBA_EXTENTS
GROUP BY FILE_ID) E
WHERE F.FILE_ID = E.FILE_ID(+)
ORDER BY 1, 2;
Claude Code를 활용해서 패키지를 작성했습니다.
$ vi pkg_datafile_resize.sql -------------------------------------------------------------------------------- -- pkg_datafile_resize : Oracle maintenance automation package (DBMS_OUTPUT only) -- ** Compatible with Oracle 11gR2 (11.2) ** -------------------------------------------------------------------------------- -- Features -- 1) move_tables : MOVE heap/partition/subpartition tables (offline) -- + auto REBUILD that table's indexes right after MOVE -- 2) rebuild_indexes : REBUILD normal/partition/subpartition indexes (standalone) -- 3) gather_stats : gather table statistics (DBMS_STATS) -- 4) resize_datafiles : shrink (reclaim) datafiles down to HWM -- -- Common notes -- * Multiple schemas : p_schemas => 'HR,SALES,APP' (comma separated) -- * DRY_RUN : p_dry_run => TRUE prints DDL only (no execution) -- p_dry_run => FALSE actually executes -- * All activity is reported via DBMS_OUTPUT only (no log table) -- => you MUST run SET SERVEROUTPUT ON beforehand -- * Each procedure calls DBMS_OUTPUT.ENABLE(NULL) to remove the buffer limit -- -- Output line format -- [DRYRUN]; -- dry_run, not executed -- [EXEC ] ; (n.nn s) -- executed OK + elapsed seconds -- [ERROR ] -> ORA-xxxxx ... -- failed (continues to next object) -- -- ** Integrated MOVE + REBUILD behavior ** -- move_tables finishes a table's MOVE, then immediately REBUILDs that table's indexes. -- - p_update_indexes=>FALSE (default): indexes become UNUSABLE on MOVE -- -> rebuilt right after (once per table, -- avoiding repeated global-index rebuilds) -- - p_update_indexes=>TRUE : MOVE keeps indexes via UPDATE INDEXES -- -> REBUILD step is auto-skipped (not needed) -- - DRY_RUN : MOVE is not executed, so indexes are still USABLE; the REBUILD -- step is printed as a "full preview". Real runs target UNUSABLE only. -- -- ** 11gR2 compatibility notes ** -- * On 11g, table MOVE is always "offline" (table is locked). -- - 11g does NOT support heap MOVE ONLINE (12.1+) or partition MOVE ONLINE (12.2+). -- - Therefore move_tables never generates an ONLINE clause for MOVE. -- * Index REBUILD ONLINE is supported on 11g EE, but BITMAP indexes cannot be -- rebuilt ONLINE (ORA-08108). -- - For BITMAP-type indexes the ONLINE clause is dropped automatically (offline rebuild). -- * ONLINE REBUILD is an Enterprise Edition feature. On SE use p_online(_rebuild)=>FALSE (default). -- * Run as SYSDBA or an account with sufficient privileges. -- -- Recommended order -- move_tables (integrated MOVE+REBUILD) -> gather_stats -> resize_datafiles -------------------------------------------------------------------------------- --============================================================================== -- [1] Package specification --============================================================================== CREATE OR REPLACE PACKAGE pkg_datafile_resize AS g_run_id VARCHAR2(30); -- Must be public (declared in the spec) because it is referenced inside SQL -- statements: TABLE(csv_to_list(...)). A body-only function raises PLS-00231. FUNCTION csv_to_list (p_csv IN VARCHAR2) RETURN SYS.ODCIVARCHAR2LIST; -- MOVE tables (normal/partition/subpartition) + REBUILD that table's indexes after MOVE PROCEDURE move_tables ( p_schemas IN VARCHAR2, -- 'HR,SALES' p_dry_run IN BOOLEAN DEFAULT TRUE, p_update_indexes IN BOOLEAN DEFAULT FALSE, -- TRUE = UPDATE INDEXES on MOVE (REBUILD step skipped) p_tab_tablespace IN VARCHAR2 DEFAULT NULL, -- target TS for the table (NULL = keep current) p_table_like IN VARCHAR2 DEFAULT NULL, -- e.g. 'T\_%' (name filter) p_rebuild_indexes IN BOOLEAN DEFAULT TRUE, -- whether to REBUILD indexes after MOVE p_online_rebuild IN BOOLEAN DEFAULT FALSE, -- ONLINE rebuild (EE only, excludes BITMAP) p_idx_tablespace IN VARCHAR2 DEFAULT NULL -- target TS for rebuilt indexes (NULL = keep current) ); -- Standalone index REBUILD (normal/partition/subpartition) -- Even with p_online=>TRUE, BITMAP-type indexes are rebuilt offline (11g/all-version limit) PROCEDURE rebuild_indexes ( p_schemas IN VARCHAR2, p_dry_run IN BOOLEAN DEFAULT TRUE, p_online IN BOOLEAN DEFAULT FALSE, -- offline by default. ONLINE is EE only (p_online=>TRUE) p_only_unusable IN BOOLEAN DEFAULT FALSE, -- TRUE = only UNUSABLE indexes p_idx_tablespace IN VARCHAR2 DEFAULT NULL -- target TS for rebuilt indexes (NULL = keep current) ); -- Gather statistics PROCEDURE gather_stats ( p_schemas IN VARCHAR2, p_dry_run IN BOOLEAN DEFAULT TRUE, p_degree IN NUMBER DEFAULT NULL, -- NULL = AUTO_DEGREE p_table_like IN VARCHAR2 DEFAULT NULL ); -- Shrink (reclaim) datafiles via RESIZE PROCEDURE resize_datafiles ( p_dry_run IN BOOLEAN DEFAULT TRUE, p_tablespaces IN VARCHAR2 DEFAULT NULL, -- NULL = all TS, comma separated allowed p_margin_mb IN NUMBER DEFAULT 100, -- free MB kept above HWM p_min_free_mb IN NUMBER DEFAULT 50 -- only resize if savings >= this value ); END pkg_datafile_resize; / SHOW ERRORS PACKAGE pkg_datafile_resize --============================================================================== -- [2] Package body --============================================================================== CREATE OR REPLACE PACKAGE BODY pkg_datafile_resize AS ---------------------------------------------------------------------------- -- Split a comma string into a collection (upper/trim). SYS.ODCIVARCHAR2LIST is OK on 11g ---------------------------------------------------------------------------- FUNCTION csv_to_list (p_csv IN VARCHAR2) RETURN SYS.ODCIVARCHAR2LIST IS v_list SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); BEGIN FOR r IN ( SELECT TRIM(UPPER(REGEXP_SUBSTR(p_csv, '[^,]+', 1, LEVEL))) tok FROM dual CONNECT BY REGEXP_SUBSTR(p_csv, '[^,]+', 1, LEVEL) IS NOT NULL ) LOOP IF r.tok IS NOT NULL THEN v_list.EXTEND; v_list(v_list.COUNT) := r.tok; END IF; END LOOP; RETURN v_list; END csv_to_list; ---------------------------------------------------------------------------- -- Execute DDL or print it (DRY_RUN). DBMS_OUTPUT only + CONTINUE on exception ---------------------------------------------------------------------------- PROCEDURE log_and_exec ( p_sql IN VARCHAR2, p_dry_run IN BOOLEAN ) IS v_start NUMBER := DBMS_UTILITY.GET_TIME; BEGIN IF p_dry_run THEN DBMS_OUTPUT.PUT_LINE('[DRYRUN] ' || p_sql || ';'); ELSE EXECUTE IMMEDIATE p_sql; DBMS_OUTPUT.PUT_LINE('[EXEC ] ' || p_sql || '; (' || TO_CHAR((DBMS_UTILITY.GET_TIME - v_start) / 100, 'FM990.00') || ' s)'); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('[ERROR ] ' || p_sql || ' -> ' || SQLERRM); END log_and_exec; ---------------------------------------------------------------------------- -- [internal helper] REBUILD all indexes of a single table (normal/partition/subpartition) -- * Assumption: index owner = table owner (typical). Indexes are found by table. -- * BITMAP-type cannot go ONLINE (ORA-08108) -> ONLINE is dropped automatically ---------------------------------------------------------------------------- PROCEDURE rebuild_idx_for_table ( p_tab_owner IN VARCHAR2, p_tab_name IN VARCHAR2, p_dry_run IN BOOLEAN, p_online IN BOOLEAN, p_only_unusable IN BOOLEAN, p_idx_tablespace IN VARCHAR2 ) IS v_sql VARCHAR2(4000); v_ts VARCHAR2(200) := CASE WHEN p_idx_tablespace IS NOT NULL THEN ' TABLESPACE "' || UPPER(p_idx_tablespace) || '"' ELSE '' END; v_online VARCHAR2(10); v_subcnt NUMBER; BEGIN FOR i IN ( SELECT owner, index_name, partitioned, status, index_type FROM dba_indexes WHERE table_owner = p_tab_owner AND table_name = p_tab_name AND index_type IN ('NORMAL','NORMAL/REV','BITMAP', 'FUNCTION-BASED NORMAL','FUNCTION-BASED BITMAP') AND index_name NOT LIKE 'BIN$%' ORDER BY owner, index_name ) LOOP -- BITMAP-type cannot be rebuilt ONLINE -> exclude ONLINE v_online := CASE WHEN p_online AND i.index_type NOT LIKE '%BITMAP%' THEN ' ONLINE' ELSE '' END; IF i.partitioned = 'NO' THEN ------------------------------------------------ normal index IF (NOT p_only_unusable) OR i.status = 'UNUSABLE' THEN v_sql := 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" REBUILD' || v_online || v_ts; log_and_exec(v_sql, p_dry_run); END IF; ELSE ------------------------------------------------ partitioned index -- whole-index REBUILD not allowed (ORA-14086) -> per partition/subpartition FOR ip IN (SELECT partition_name, status FROM dba_ind_partitions WHERE index_owner = i.owner AND index_name = i.index_name) LOOP SELECT COUNT(*) INTO v_subcnt FROM dba_ind_subpartitions WHERE index_owner = i.owner AND index_name = i.index_name AND partition_name = ip.partition_name; IF v_subcnt = 0 THEN -- single-level partitioned index IF (NOT p_only_unusable) OR ip.status = 'UNUSABLE' THEN v_sql := 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" REBUILD PARTITION "' || ip.partition_name || '"' || v_online || v_ts; log_and_exec(v_sql, p_dry_run); END IF; ELSE -- local subpartitioned index (avoid ORA-14287 -> per SUBPARTITION) FOR isp IN (SELECT subpartition_name, status FROM dba_ind_subpartitions WHERE index_owner = i.owner AND index_name = i.index_name AND partition_name = ip.partition_name) LOOP IF (NOT p_only_unusable) OR isp.status = 'UNUSABLE' THEN v_sql := 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" REBUILD SUBPARTITION "' || isp.subpartition_name || '"' || v_online || v_ts; log_and_exec(v_sql, p_dry_run); END IF; END LOOP; END IF; END LOOP; END IF; END LOOP; END rebuild_idx_for_table; ---------------------------------------------------------------------------- -- 1) MOVE tables (+ REBUILD that table's indexes right after MOVE) -- 11g: MOVE is always offline (no ONLINE clause generated) ---------------------------------------------------------------------------- PROCEDURE move_tables ( p_schemas IN VARCHAR2, p_dry_run IN BOOLEAN DEFAULT TRUE, p_update_indexes IN BOOLEAN DEFAULT FALSE, p_tab_tablespace IN VARCHAR2 DEFAULT NULL, p_table_like IN VARCHAR2 DEFAULT NULL, p_rebuild_indexes IN BOOLEAN DEFAULT TRUE, p_online_rebuild IN BOOLEAN DEFAULT FALSE, p_idx_tablespace IN VARCHAR2 DEFAULT NULL ) IS v_sql VARCHAR2(4000); v_ts VARCHAR2(200) := CASE WHEN p_tab_tablespace IS NOT NULL THEN ' TABLESPACE "' || UPPER(p_tab_tablespace) || '"' ELSE '' END; v_upd VARCHAR2(20) := CASE WHEN p_update_indexes THEN ' UPDATE INDEXES' ELSE '' END; v_part VARCHAR2(3); v_subtype VARCHAR2(30); BEGIN DBMS_OUTPUT.ENABLE(NULL); -- remove buffer size limit g_run_id := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD_HH24MISS'); DBMS_OUTPUT.PUT_LINE('=== MOVE TABLES (+REBUILD) (DRY_RUN=' || CASE WHEN p_dry_run THEN 'TRUE' ELSE 'FALSE' END || ') run_id=' || g_run_id || ' ==='); FOR t IN ( SELECT owner, table_name FROM dba_tables WHERE owner IN (SELECT column_value FROM TABLE(csv_to_list(p_schemas))) AND temporary = 'N' AND (nested IS NULL OR nested = 'NO') AND iot_type IS NULL AND secondary = 'N' AND table_name NOT LIKE 'BIN$%' AND (p_table_like IS NULL OR table_name LIKE UPPER(p_table_like)) ORDER BY owner, table_name ) LOOP ------------------------------------------------------------------ MOVE SELECT partitioned INTO v_part FROM dba_tables WHERE owner = t.owner AND table_name = t.table_name; IF v_part = 'NO' THEN -- normal table v_sql := 'ALTER TABLE "' || t.owner || '"."' || t.table_name || '" MOVE' || v_ts || v_upd; log_and_exec(v_sql, p_dry_run); ELSE SELECT NVL(MAX(subpartitioning_type), 'NONE') INTO v_subtype FROM dba_part_tables WHERE owner = t.owner AND table_name = t.table_name; IF v_subtype = 'NONE' THEN -- single-level partitioned table FOR pp IN (SELECT partition_name FROM dba_tab_partitions WHERE table_owner = t.owner AND table_name = t.table_name ORDER BY partition_position) LOOP v_sql := 'ALTER TABLE "' || t.owner || '"."' || t.table_name || '" MOVE PARTITION "' || pp.partition_name || '"' || v_ts || v_upd; log_and_exec(v_sql, p_dry_run); END LOOP; ELSE -- composite (subpartitioned) table FOR sp IN (SELECT subpartition_name FROM dba_tab_subpartitions WHERE table_owner = t.owner AND table_name = t.table_name ORDER BY subpartition_position) LOOP v_sql := 'ALTER TABLE "' || t.owner || '"."' || t.table_name || '" MOVE SUBPARTITION "' || sp.subpartition_name || '"' || v_ts || v_upd; log_and_exec(v_sql, p_dry_run); END LOOP; END IF; END IF; ---------------------------------------------------------------- REBUILD -- If MOVE kept indexes (UPDATE INDEXES), no REBUILD is needed -> SKIP IF p_rebuild_indexes AND NOT p_update_indexes THEN -- Real run: target only those made UNUSABLE by MOVE (p_only_unusable=>TRUE) -- DRY_RUN : MOVE not executed so status is USABLE; preview all (p_only_unusable=>FALSE) rebuild_idx_for_table( p_tab_owner => t.owner, p_tab_name => t.table_name, p_dry_run => p_dry_run, p_online => p_online_rebuild, p_only_unusable => NOT p_dry_run, p_idx_tablespace => p_idx_tablespace); END IF; END LOOP; END move_tables; ---------------------------------------------------------------------------- -- 2) Standalone index REBUILD (all indexes of tables in the given schemas) ---------------------------------------------------------------------------- PROCEDURE rebuild_indexes ( p_schemas IN VARCHAR2, p_dry_run IN BOOLEAN DEFAULT TRUE, p_online IN BOOLEAN DEFAULT FALSE, p_only_unusable IN BOOLEAN DEFAULT FALSE, p_idx_tablespace IN VARCHAR2 DEFAULT NULL ) IS BEGIN DBMS_OUTPUT.ENABLE(NULL); -- remove buffer size limit g_run_id := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD_HH24MISS'); DBMS_OUTPUT.PUT_LINE('=== REBUILD INDEXES (DRY_RUN=' || CASE WHEN p_dry_run THEN 'TRUE' ELSE 'FALSE' END || ') run_id=' || g_run_id || ' ==='); -- group by target table and reuse rebuild_idx_for_table FOR t IN ( SELECT DISTINCT table_owner, table_name FROM dba_indexes WHERE owner IN (SELECT column_value FROM TABLE(csv_to_list(p_schemas))) AND index_type IN ('NORMAL','NORMAL/REV','BITMAP', 'FUNCTION-BASED NORMAL','FUNCTION-BASED BITMAP') AND index_name NOT LIKE 'BIN$%' ORDER BY table_owner, table_name ) LOOP rebuild_idx_for_table( p_tab_owner => t.table_owner, p_tab_name => t.table_name, p_dry_run => p_dry_run, p_online => p_online, p_only_unusable => p_only_unusable, p_idx_tablespace => p_idx_tablespace); END LOOP; END rebuild_indexes; ---------------------------------------------------------------------------- -- 3) Gather statistics (DBMS_STATS - all args supported on 11g) ---------------------------------------------------------------------------- PROCEDURE gather_stats ( p_schemas IN VARCHAR2, p_dry_run IN BOOLEAN DEFAULT TRUE, p_degree IN NUMBER DEFAULT NULL, p_table_like IN VARCHAR2 DEFAULT NULL ) IS v_desc VARCHAR2(4000); v_deg VARCHAR2(60) := CASE WHEN p_degree IS NULL THEN 'DBMS_STATS.AUTO_DEGREE' ELSE TO_CHAR(p_degree) END; BEGIN DBMS_OUTPUT.ENABLE(NULL); -- remove buffer size limit g_run_id := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD_HH24MISS'); DBMS_OUTPUT.PUT_LINE('=== GATHER STATS (DRY_RUN=' || CASE WHEN p_dry_run THEN 'TRUE' ELSE 'FALSE' END || ') run_id=' || g_run_id || ' ==='); FOR t IN ( SELECT owner, table_name FROM dba_tables WHERE owner IN (SELECT column_value FROM TABLE(csv_to_list(p_schemas))) AND temporary = 'N' AND iot_type IS NULL AND (nested IS NULL OR nested = 'NO') AND secondary = 'N' AND table_name NOT LIKE 'BIN$%' AND (p_table_like IS NULL OR table_name LIKE UPPER(p_table_like)) ORDER BY owner, table_name ) LOOP v_desc := 'DBMS_STATS.GATHER_TABLE_STATS(ownname=>''' || t.owner || ''', tabname=>''' || t.table_name || ''', estimate_percent=>AUTO_SAMPLE_SIZE' || ', method_opt=>''FOR ALL COLUMNS SIZE AUTO''' || ', degree=>' || v_deg || ', granularity=>''AUTO'', cascade=>TRUE)'; IF p_dry_run THEN DBMS_OUTPUT.PUT_LINE('[DRYRUN] ' || v_desc || ';'); ELSE DECLARE v_start NUMBER := DBMS_UTILITY.GET_TIME; BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => t.owner, tabname => t.table_name, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => CASE WHEN p_degree IS NULL THEN DBMS_STATS.AUTO_DEGREE ELSE p_degree END, granularity => 'AUTO', cascade => TRUE); DBMS_OUTPUT.PUT_LINE('[EXEC ] ' || v_desc || '; (' || TO_CHAR((DBMS_UTILITY.GET_TIME - v_start) / 100, 'FM990.00') || ' s)'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('[ERROR ] ' || t.owner || '.' || t.table_name || ' -> ' || SQLERRM); END; END IF; END LOOP; END gather_stats; ---------------------------------------------------------------------------- -- 4) Shrink (reclaim) datafiles via RESIZE -- Cannot shrink below HWM (highest used block) (ORA-03297) -> keep a margin -- Note: dba_extents.file_id is the absolute file number. Verify results on bigfile TS. -- Exclude UNDO/SYSTEM etc. explicitly via p_tablespaces when appropriate. ---------------------------------------------------------------------------- PROCEDURE resize_datafiles ( p_dry_run IN BOOLEAN DEFAULT TRUE, p_tablespaces IN VARCHAR2 DEFAULT NULL, p_margin_mb IN NUMBER DEFAULT 100, p_min_free_mb IN NUMBER DEFAULT 50 ) IS v_sql VARCHAR2(4000); v_cur_mb NUMBER; v_new_mb NUMBER; v_save_mb NUMBER; BEGIN DBMS_OUTPUT.ENABLE(NULL); -- remove buffer size limit g_run_id := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD_HH24MISS'); DBMS_OUTPUT.PUT_LINE('=== RESIZE DATAFILES reclaim (DRY_RUN=' || CASE WHEN p_dry_run THEN 'TRUE' ELSE 'FALSE' END || ') run_id=' || g_run_id || ' ==='); FOR f IN ( SELECT ddf.file_id, ddf.file_name, ddf.tablespace_name, ddf.bytes, ts.block_size, (SELECT NVL(MAX(e.block_id + e.blocks - 1), 1) FROM dba_extents e WHERE e.file_id = ddf.file_id) AS hwm_blocks FROM dba_data_files ddf JOIN dba_tablespaces ts ON ts.tablespace_name = ddf.tablespace_name WHERE (p_tablespaces IS NULL OR ddf.tablespace_name IN (SELECT column_value FROM TABLE(csv_to_list(p_tablespaces)))) ) LOOP v_cur_mb := ROUND(f.bytes / 1024 / 1024); v_new_mb := CEIL(f.hwm_blocks * f.block_size / 1024 / 1024) + p_margin_mb; v_save_mb := v_cur_mb - v_new_mb; IF v_save_mb >= p_min_free_mb THEN v_sql := 'ALTER DATABASE DATAFILE ''' || f.file_name || ''' RESIZE ' || v_new_mb || 'M'; DBMS_OUTPUT.PUT_LINE('-- ' || f.tablespace_name || ' file#' || f.file_id || ' cur=' || v_cur_mb || 'MB -> new=' || v_new_mb || 'MB (save ' || v_save_mb || 'MB)'); log_and_exec(v_sql, p_dry_run); END IF; END LOOP; END resize_datafiles; END pkg_datafile_resize; / SHOW ERRORS PACKAGE BODY pkg_datafile_resize --============================================================================== -- [3] Usage examples (run SET SERVEROUTPUT ON first) --============================================================================== -- SET SERVEROUTPUT ON SIZE UNLIMITED -- SET LINESIZE 200 -- -- -- 1) Integrated MOVE+REBUILD: DRY_RUN to preview MOVE/REBUILD DDL -- EXEC pkg_datafile_resize.move_tables(p_schemas=>'HR,SALES', p_dry_run=>TRUE); -- -- -- 2) Real run: offline MOVE -> rebuild UNUSABLE indexes right after -- EXEC pkg_datafile_resize.move_tables(p_schemas=>'HR,SALES', p_dry_run=>FALSE, p_tab_tablespace=>'DATA02'); -- -- indexes to another TS: ... p_idx_tablespace=>'IDX02' -- -- rebuild ONLINE (EE): ... p_online_rebuild=>TRUE -- -- keep indexes during MOVE (auto-skip rebuild): ... p_update_indexes=>TRUE -- -- MOVE only, no rebuild: ... p_rebuild_indexes=>FALSE -- -- -- 3) Standalone index REBUILD (e.g. fix previously UNUSABLE indexes) -- EXEC pkg_datafile_resize.rebuild_indexes(p_schemas=>'HR,SALES', p_dry_run=>FALSE, p_only_unusable=>TRUE); -- -- rebuild into a specific index TS: ... p_idx_tablespace=>'IDX02' -- -- -- 4) Gather stats / shrink datafiles -- EXEC pkg_datafile_resize.gather_stats(p_schemas=>'HR,SALES', p_dry_run=>FALSE); -- EXEC pkg_datafile_resize.resize_datafiles(p_dry_run=>FALSE, p_tablespaces=>'USERS', p_margin_mb=>200, p_min_free_mb=>100); -- -- -- Keep output to a file: SPOOL maint_run.log ... SPOOL OFF -- -- -- 5) After resizing datafiles, drop package pkg_datafile_resize -- DROP PACKAGE pkg_datafile_resize; --------------------------------------------------------------------------------
SQL*Plus에서 @pkg_datafile_resize.sql을 실행하면, 패키지 스펙과 바디가 컴파일되어 데이터베이스에 저장(등록) 됩니다.
SQL> @pkg_datafile_resize.sql Package created. No errors. Package body created. No errors. SQL> COL object_name FOR A30; COL object_type FOR A30; COL STATUS FOR A8; SELECT object_name, object_type, status FROM dba_objects WHERE object_name = 'PKG_DATAFILE_RESIZE'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------------------ -------- PKG_DATAFILE_RESIZE PACKAGE VALID PKG_DATAFILE_RESIZE PACKAGE BODY VALID
SQL*Plus 프롬프트에서 아래 순서대로 사용하시면 됩니다.
- DBMS_OUTPUT.PUT_LINE으로 버퍼에 쌓인 내용이 화면에 보이게 설정합니다.
SQL> SET SERVEROUTPUT ON SIZE UNLIMITED; SQL> SET LINESIZE 300;
- DRY_RUN으로 특정 스키마(들)의 TABLE MOVE 및 INDEX REBUILD를 위한 DDL을 확인합니다.
SQL> EXEC pkg_datafile_resize.move_tables(p_schemas=>'ITOHPJT', p_dry_run=>TRUE); === MOVE TABLES (+REBUILD) (DRY_RUN=TRUE) run_id=20260612_134021 === [DRYRUN] ALTER TABLE "ITOHPJT"."XIF_INFO_MAS" MOVE; [DRYRUN] ALTER TABLE "ITOHPJT"."XIF_INFO_MAS_SI" MOVE; .... [DRYRUN] ALTER INDEX "ITOHPJT"."Relationship_3_FK" REBUILD; PL/SQL procedure successfully completed.
- p_dry_run=>FALSE로 변경하여 TABLE MOVE를 실행합니다.
SQL> EXEC pkg_datafile_resize.move_tables(p_schemas=>'ITOHPJT', p_dry_run=>FALSE); === MOVE TABLES (+REBUILD) (DRY_RUN=FALSE) run_id=20260612_135433 === [EXEC ] ALTER TABLE "ITOHPJT"."XIF_INFO_MAS" MOVE; (0.05 s) [EXEC ] ALTER TABLE "ITOHPJT"."XIF_INFO_MAS_SI" MOVE; (0.02 s) .... [EXEC ] ALTER INDEX "ITOHPJT"."Relationship_3_FK" REBUILD; (3.30 s) PL/SQL procedure successfully completed.
처음에 실행한 쿼리를 다시 실행하여 TABLE MOVE 및 INDEX REBUILD 이후의 datafile 상태를 확인합니다.
- TB_ITOH_DATA 테이블스페이스의 경우, 절감가능 크기가 1175MB → 823MB로 감소
TABLE MOVE 직후 HWM이 상승하여 데이터파일이 더 뚱뚱해지는 기현상이 발생하기도 합니다. PCTFREE(여유공간) 규격을 엄격히 준수하게 되거나, 내부 구조(Row Chaining)가 올바르게 치유되면서 일시적으로 공간을 더 많이 써서 발생합니다.(이 현상은 실패한 것이 아니라 데이터베이스의 물리적 건강 상태가 훨씬 좋아진 증거이므로 안심하셔도 됩니다.) - TB_ITOH_INDEX 테이블스페이스의 경우, 절감가능 크기가 407MB → 511MB로 증가
인덱스는 구조 특성상 데이터가 삭제되면 빈 공간(Empty Leaf Block)이 그대로 남아 껍데기만 비대해집니다. REBUILD를 하면 이 뒤틀린 밸런스 트리를 완전히 허물고 가장 압축된 최적의 형태로 지도를 다시 그리기 때문에 차지하던 블록 수가 급격히 줄어들고 HWM이 내려갑니다.
