[Oracle] 테이블스페이스가 충분한데 INSERT가 안된다 (ORA-01654)

1. 문제상황

 - 테이블 스페이스의 여유공간이 충분한데 INSERT가 안된다.

 

 - 오류코드 

ORA-01654 : unable to extend index AAAA by 8192 in tablespace BBBB.

ORA-01654 : AAAA 인덱스를 8192(으)로 BBBB 테이블스페이스에 확장할 수 없습니다.

 

 → 일반적으로 위 에러코드는 인덱스의 테이블스페이스의 여유공간 부족으로 인해 생겼을 확률이 높아

     테이블스페이스를 추가해주면 해결 가능하다.

     그런데, 테이블스페이스의 충분한 여유공간에도 에러가 발생한게 이해가 안갔다. 

 

 

 

2. 해결과정

 1) 가장 먼저 의심한 것은 RECYCLE BIN에 있는 데이터가 제대로 PURGE 되지 않은 상황

    → 확인 결과 해당 테이블스페이스에 있었던 RECYCLE BIN OBJECT 없음

 

 2) 테이블스페이스를 구성하는 데이터파일이 깨졌을 확률 존재 (물리적 혹은 베드섹터 등)

    → 운영DB와 동일한 데이터를 갖고 있는 검증DB에서도 동일한 현상 발생

        → 물리적 혹은 데이터파일, LVOL 등의 문제가 다른 DB에서 우연히 겹치기는 확률적으로 매우 낮음

 

 3) 오라클 DOCS + 구글링을 통해 에러코드의 정확한 의미 파악

    → 에러코드의 의미상 숫자 8192는 8K의 용량이 아니라 BLOCK의 개수를 의미함

        → 8192 * 8K = 64MB로 만약에 테이블스페이스의 여유공간이 크더라도, 

           가장 큰 연속 EXTENT의 크기 (largest contiguous extent size)가 64MB보다 작다면 해당 오류 발생가능

 

            → 테이블스페이스의 여유공간의 합이 충분히 많더라도,

                할당할 수 있는 연속된 EXTENT의 합이 작다면 에러가 발생할 수 있다. 

 

                → 결론적으로 테이블스페이스에 여유공간을 추가해줘서 해결

 

 

ORA-01654: unable to extend index string.string by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for an index segment in the tablespace indicated.

 

출처 : docs.oracle.com/cd/B28359_01/server.111/b28278/e1500.htm#ORA-01500

 

 

 

3. 위의 논리가 가능한 이유들 (배경지식)

 

1) 테이블스페이스의 할당 (ALLOCATION)은 옵션에 따라 두 가지 종류로 나뉜다.

   - AUTOALLOCATE : 오라클 내부 로직에 의해 64KB / 1MB / 64MB 로 ALLOCATION이 발생한다.

                             소량의 INSERT가 예상될 경우 64KB를 ALLOCATION,

                             대량의 INSERT가 예상될 경우 64MB를 ALLOCATION해 효율을 높이는 방식이다.

   - UNIFORM : 테이블스페이스를 할당할때 무조건 정해진 용량을 할당하는 방식이다. 

                     DEFAULT SIZE는 1MB이며, 다른 크기로도 설정이 가능하다.

 

참고URL : docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11359

 

 

2) 테이블스페이스의 FREE EXTENT들의 합을 여유공간으로 생각하면 안된다. 

   → 오직 연속된 EXTENT(CONTIGUOUS EXTENT) 만을 할당할 수 있다.

 

    아래 확인 쿼리를 실행했을때 나오는 

     TOTAL_FREE_SPACE는 여유공간들의 단순 합이고

     LARGEST_CONTIGUOUS_FREE_EXTENT는 할당할 수 있는 최대 크기이다.

 

    즉, 아무리 TOTAL_FREE_SPACE가 크더라도, 남은 공간이 단편화되서 산산히 쪼개져 있다면 

    LARGEST_CONTIGUOUS_FREE_EXTENT의 크기가 작아 적은 용량의 데이터가 들어와도 할당이 안된다.

 

확인 쿼리 : 

SELECT ROUND(SUM(BYTES)/1024/1024) "TOTAL_FREE_SPACE(MB)",
       ROUND(MAX(BYTES)/1024/1024) "LARGEST_CONTIGUOUS_FREE_EXTENT(MB)"
FROM   DBA_FREE_SPACE
WHERE  TABLESPACE_NAME = :tableSpaceName;

참고URL : docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm

 

  3) UPDATE가 빈번한 테이블의 경우 인덱스리빌드를 주기적으로 해주지 않는다면

    인덱스 테이블스페이스가 단편화되서 남은 LARGEST_CONTIGUOUS_FREE_EXTENT의 크기가 작을 수 있다.

 

  4) 인덱스 테이블스페이스 단편화로 남아있는 최대 연속 EXTENT의 크기가 64MB 미만인데,

     대량의 INSERT가 예상된다면 오라클은 한번에 64MB를 할당하게 되고,

     이때문에 ORA-01654가 발생할 수 있다.

     → 주기적 인덱스리빌드, 또는 작은 테이블의 경우 UNIFORM 옵션을 통해 보완 가능

 

 

 

4. 오늘의 결론

   - 위 에러 발생시 긴급 조치 방법은 테이블스페이스를 추가하는 것이지만,

     장기적으로는 주기적인 인덱스리빌드를 통해 인덱스 테이블스페이스의 단편화를 방지해야한다.