[Oracle] java.sql.SQLException: ORA-01654 (unable to extend index #dbName.#indexName by 8192 in tablespace #tablespaceName)

에러 메시지

org.springframework.jdbc.UncategorizedSQLException:
### Error updating database.  Cause: java.sql.SQLException: ORA-01654: unable to extend index [DB명].[index명] by 8192 in tablespace [tablespace명]

### The error may exist in file [/usr/local/tomcat8/webapps/ROOT/WEB-INF/classes/sql/파일명.xml]
### The error may involve db.dao.DAO명.메서드명-Inline
### The error occurred while setting parameters
### SQL: insert into 테이블명        ( 쿼리)
### Cause: java.sql.SQLException: ORA-01654: unable to extend index [DB명].[index명] by 8192 in tablespace [tablespace명]

; uncategorized SQLException; SQL state [72000]; error code [1654]; ORA-01654: unable to extend index [DB명].[index명] by 8192 in tablespace [tablespace명]
; nested exception is java.sql.SQLException: ORA-01654: unable to extend index [DB명].[index명] by 8192 in tablespace [tablespace명]

        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
        at com.sun.proxy.$Proxy72.insert(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:279)

 

Oracle DB의 인덱스 공간 부족으로 해당 에러를 만났다.

우연히 모니터링 하다가 발견한 오류.

 

이 같은 문제는 해당 인덱스의 공간을 늘려주면 해결된다.

---

1. 원인

ORA-01654: unable to extend index [DB명].[index명] by 8192 in tablespace [tablespace명]

Oracle이 해당 인덱스를 저장할 공간을 관련된 테이블스페이스에서 더 이상 확보할 수 없다는 뜻이다.

즉, 인덱스가 커졌는데 테이블스페이스에 여유 공간이 부족하다는 의미이다.

 

insert 구문이 해당 인덱스를 사용하는 테이블에 데이터를 넣을 때,

인덱스도 업데이트 되어야 하므로 인덱스 공간이 필요해서 생긴 일이다.

 

2. 해결

인덱스 사이즈 조회

SELECT
    a.tablespace_name,
    ROUND(a.total_mb, 2) AS total_mb,
    ROUND(b.used_mb, 2) AS used_mb,
    ROUND(a.total_mb - b.used_mb, 2) AS free_mb,
    ROUND((b.used_mb / a.total_mb) * 100, 2) AS used_pct
FROM
    (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb
     FROM dba_data_files
     GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS used_mb
     FROM dba_segments
     GROUP BY tablespace_name) b
WHERE
    a.tablespace_name = b.tablespace_name
    AND a.tablespace_name = '테이블스페이스명';

 

쿼리 실행 결과)

TABLESPACE_NAME	TOTAL_MB	USED_MB	FREE_MB	USED_PCT
테이블스페이스명	10,000	9,500	500	95%

 

대충 이런 식으로 나온다. (임의로 숫자를 넣은거니 흐린눈 부탁)

 

나의 경우, 해당 테이블스페이스의 공간에서 95%의 데이터가 공간을 차지하여 여유공간이 남아있지 않기에

위와 같은 에러가 발생한 것이다.

 

▶️ [기타 해결 방법]

(1) 인덱스를 다른 테이블스페이스로 이동

ALTER INDEX [DB명].[인덱스명] REBUILD TABLESPACE 다른_테이블스페이스;

 

(2) 불필요한 인덱스 제거

사용 빈도 낮은 인덱스를 조회하여 오래된 인덱스나 불필요한 인덱스를 제거한다.

SELECT * FROM DBA_INDEX_USAGE WHERE INDEX_NAME = '인덱스명';

 

(3) 테이블스페이스 자동 공간 확장

자동 확장 설정을 확인하고,

SELECT FILE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 AS SIZE_MB 
FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = '테이블스페이스명';

AUTOEXTEND를 활성화시킨다.

ALTER DATABASE DATAFILE '/경로/테이블스페이스명.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

 

(4) 테이블스페이스에 데이터 파일 추가

ALTER TABLESPACE 테이블스페이스명 
ADD DATAFILE '/경로/다른 테이블스페이스명.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

 

실제 해결법) 용량 증설에 아래 쿼리 이용

ALTER DATABASE DATAFILE '/경로/파일명.dbf' RESIZE 30G;

-- 또는

ALTER DATABASE DATAFILE '/경로/파일명.dbf' AUTOEXTEND ON MAXSIZE 30G;

 

용량을 대폭 늘렸다.

 

* 참고로 오라클 테이블스페이스 용량은 딱히 제한되어 있진 않다고는 하는데, 

궁극적으로는 서버의 디스크 공간이 실제 최대 한계를 결정하다고 한다.

이론적으로는 무제한이지만 데이터파일 최대 크기, os 파일 시스템 한계, 디스크 공간의 제약을 받아서 점차점차 상황과 스펙에 맞게 늘려가면 될 듯 하다.