시스템 관리_31 오라클 ORA-01653 unable to extend table 테이블스페이스 공간 부족 오류 원인 분석 및 긴급 증설 해결 가이드

# 1. 오라클 alert.log 파일에서 ORA-01653 발생 내역 확인
$ tail -n 50 $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log
# [출력 예시]
# ORA-01653: unable to extend table SCOTT.ORDER_HISTORY by 8192 in tablespace USERS
# Completed checkpoint up to RBA ...

# 2. OS 레벨에서 오라클 데이터파일이 위치한 파일시스템 디스크 여유 공간 확인
$ df -h /u01 /u02 /data
# Filesystem      Size  Used Avail Use% Mounted on
# /dev/sdb1       500G  485G  2.1G  99% /data
# -> 디스크 마운트 포인트 자체가 99%에 도달했는지 확인

# 3. SQL*Plus로 접속하여 테이블스페이스별 가용량 및 사용률 조회
$ sqlplus -s / as sysdba << 'EOF'
SET LINESIZE 200 PAGESIZE 100
COL tablespace_name FORMAT A20
COL total_mb FORMAT 999,999,999
COL used_mb FORMAT 999,999,999
COL free_mb FORMAT 999,999,999
COL used_pct FORMAT 990.99

SELECT 
    d.tablespace_name,
    ROUND(d.bytes / 1024 / 1024) AS total_mb,
    ROUND((d.bytes - NVL(f.bytes, 0)) / 1024 / 1024) AS used_mb,
    ROUND(NVL(f.bytes, 0) / 1024 / 1024) AS free_mb,
    ROUND(((d.bytes - NVL(f.bytes, 0)) / d.bytes) * 100, 2) AS used_pct
FROM 
    (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_data_files GROUP BY tablespace_name) d
    LEFT JOIN 
    (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_free_space GROUP BY tablespace_name) f
    ON d.tablespace_name = f.tablespace_name
ORDER BY used_pct DESC;
EXIT;
EOF

# 4. 대상 테이블스페이스에 속한 데이터파일 목록 및 자동확장(AUTOEXTEND) 여부 조회
$ sqlplus -s / as sysdba << 'EOF'
SET LINESIZE 200 PAGESIZE 100
COL file_name FORMAT A50
COL autoextensible FORMAT A10
COL maxbytes_mb FORMAT 999,999,999

SELECT 
    file_id, 
    file_name, 
    ROUND(bytes/1024/1024) AS size_mb, 
    autoextensible, 
    ROUND(maxbytes/1024/1024) AS maxbytes_mb
FROM dba_data_files
WHERE tablespace_name = 'USERS';
EXIT;
EOF

#!/bin/bash
# check_oracle_tablespace.sh - Diagnose Oracle Tablespace usage and ORA-01653 risks

export ORACLE_SID=${ORACLE_SID:-"orcl"}
export ORAENV_ASK=NO
[ -f /usr/local/bin/oraenv ] && . /usr/local/bin/oraenv >/dev/null 2>&1

echo "=========================================="
echo " [오라클 테이블스페이스 공간 진단] SID: ${ORACLE_SID}"
echo "=========================================="

# 1. 오라클 인스턴스 접속 가능 여부 점검
if ! command -v sqlplus &>/dev/null; then
    echo "  [오류] sqlplus 명령어를 찾을 수 없습니다. 환경변수(ORACLE_HOME/PATH)를 확인하세요."
    exit 1
fi

# 2. 테이블스페이스 사용률 점검 쿼리 실행
SQL_OUTPUT=$(sqlplus -s / as sysdba << 'EOF'
SET FEEDBACK OFF HEADING OFF ECHO OFF VERIFY OFF LINESIZE 300
SELECT 
    d.tablespace_name || '|' ||
    ROUND(d.bytes / 1024 / 1024) || '|' ||
    ROUND((d.bytes - NVL(f.bytes, 0)) / 1024 / 1024) || '|' ||
    ROUND(NVL(f.bytes, 0) / 1024 / 1024) || '|' ||
    TO_CHAR(ROUND(((d.bytes - NVL(f.bytes, 0)) / d.bytes) * 100, 2), 'FM990.00')
FROM 
    (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_data_files GROUP BY tablespace_name) d
    LEFT JOIN 
    (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_free_space GROUP BY tablespace_name) f
    ON d.tablespace_name = f.tablespace_name
ORDER BY 5 DESC;
EXIT;
EOF
)

if [ -z "$SQL_OUTPUT" ]; then
    echo "  [오류] 테이블스페이스 정보를 조회할 수 없습니다. DB 상태를 확인하세요."
    exit 1
fi

echo -e "\n[1] 테이블스페이스별 점유율 분석:"
printf "  %-20s | %-12s | %-12s | %-12s | %-8s\n" "테이블스페이스명" "전체(MB)" "사용(MB)" "여유(MB)" "사용률(%)"
echo "  -------------------------------------------------------------------------------"

CRITICAL_TS=()

while IFS='|' read -r TS_NAME TOTAL_MB USED_MB FREE_MB USED_PCT; do
    [ -z "$TS_NAME" ] && continue
    printf "  %-20s | %-12s | %-12s | %-12s | %-8s\n" "$TS_NAME" "$TOTAL_MB" "$USED_MB" "$FREE_MB" "${USED_PCT}%"
    
    IS_CRITICAL=$(echo "$USED_PCT >= 90.0" | bc -l 2>/dev/null)
    if [ "$IS_CRITICAL" -eq 1 ]; then
        CRITICAL_TS+=("$TS_NAME")
    fi
done <<< "$SQL_OUTPUT"

# 3. 고갈 임박 테이블스페이스에 대한 세부 데이터파일 진단
echo -e "\n[2] 고갈 임박 테이블스페이스(90% 이상) 데이터파일 세부 분석:"
if [ ${#CRITICAL_TS[@]} -eq 0 ]; then
    echo "  [정상] 사용률 90%를 초과하는 위험 테이블스페이스가 없습니다."
else
    for TS in "${CRITICAL_TS[@]}"; do
        echo "  [경고] 테이블스페이스 '${TS}' 정밀 진단:"
        
        DATAFILE_INFO=$(sqlplus -s / as sysdba << EOF
SET FEEDBACK OFF HEADING OFF ECHO OFF VERIFY OFF LINESIZE 300
SELECT 
    file_name || '|' ||
    ROUND(bytes/1024/1024) || '|' ||
    autoextensible || '|' ||
    ROUND(maxbytes/1024/1024)
FROM dba_data_files
WHERE tablespace_name = '${TS}';
EXIT;
EOF
)
        while IFS='|' read -r F_NAME F_SIZE F_AUTO F_MAX; do
            [ -z "$F_NAME" ] && continue
            echo "    - 파일: $F_NAME"
            echo "      현재 크기: ${F_SIZE}MB | 자동확장: ${F_AUTO} | 최대 한도: ${F_MAX}MB"
            if [ "$F_AUTO" == "NO" ]; then
                echo "      [!] AUTOEXTEND가 비활성화되어 있습니다. 추가 할당이 불가능합니다."
            elif [ "$F_SIZE" -ge "$F_MAX" ] && [ "$F_MAX" -gt 0 ]; then
                echo "      [!] 파일이 설정된 최대 한도(MAXBYTES)에 이미 도달했습니다."
            fi
        done <<< "$DATAFILE_INFO"
    done
fi

echo -e "\n=========================================="
echo " [진단 완료]"
echo "=========================================="