ORA-01653: unable to extend table in tablespace 오류 해결 방법

ORA-01653: unable to extend table in tablespace 오류 해결 방법

 

ORA-01653: unable to extend table in tablespace

해당 포스팅은 오라클 데이터베이스 사용 중 데이터 insert 과정에서 발생할 수 있는 'ORA-01653: unable to extend table in tablespace' 오류에 대한 해결 방법을 정리한 것입니다.

 

---

오류가 발생한 원인

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

 

오류가 발생한 원인은 Cause 부분을 통해서 알 수 있는 것처럼 테이블스페이스의 세그먼트(segment)를 할당하지 못했기 때문인데요.

쉽게 말하면 해당 테이블스페이스(tablespace)의 용량이 부족하기 때문에 발생하는 문제입니다.

 

2024.11.17 - [Programming/Oracle] - Oracle TABLESPACE란? 테이블스페이스 개념 정리

(tablespace 및 segment에 대한 세부적인 개념이 궁금하시다면 해당 포스팅을 참고하시면 됩니다.)

 

SELECT
    df.tablespace_name,
    ROUND(NVL(df.total_space, 0) / 1024 / 1024, 2) AS "할당용량(MB)",
    ROUND(NVL(fs.free_space, 0) / 1024 / 1024, 2) AS "여유용량(MB)",
    ROUND(((NVL(df.total_space, 0)) - (NVL(fs.free_space, 0))) / 1024 / 1024, 2) AS "사용량(MB)",
    ROUND((((NVL(df.total_space, 0)) - (NVL(fs.free_space, 0))) / df.total_space) * 100, 2) AS "사용률(%)",
    (SELECT autoextensible
     FROM dba_data_files
     WHERE tablespace_name = df.tablespace_name) AS "자동확장여부"
FROM
    (SELECT
        tablespace_name,
        SUM(bytes) AS total_space
     FROM
        dba_data_files
     GROUP BY
        tablespace_name) df
JOIN
    (SELECT
        tablespace_name,
        SUM(bytes) AS free_space
     FROM
        dba_free_space
     GROUP BY
        tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name(+)
ORDER BY df.tablespace_name;

(tablespace 용량 조회 쿼리)

 

tablespace 용량 및 자동확장 여부 조회 결과

위 쿼리를 통해 오류가 발생했던 테이블스페이스(USERS)의 용량을 확인해 보면 다음과 같이 테이블스페이스의 여유 용량이 없다는 것을 확인할 수 있으며, 자동 확장 여부도 활성화되지 않은 것을 확인할 수 있습니다.

 

 

---

해결 방법

1. tablespace의 용량을 resize를 통해 늘려주거나 autoextend를 활성화시켜 자동으로 용량을 확장하도록 하는 방법

2. tablespace에 datafile을 추가하는 방법

 

문제를 해결하는 방법에는 크게 두 가지 방법이 있는데요.

 

SELECT tablespace_name, file_name, bytes/1024/1024 AS SIZE_MB, status, autoextensible
FROM dba_data_files
WHERE tablespace_name = 'USERS';

(tablespace datafile 정보 조회 쿼리)

 

tablespace datafile 정보 조회 결과

먼저 다음 쿼리를 통해 대상 테이블스페이스의 'file_name'을 확인합니다.

 

 

-- RESIZE를 통한 데이터 파일 크기 수정
ALTER DATABASE DATAFILE '데이터파일' RESIZE 파일용량;
-- 실제 쿼리 예시
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf' RESIZE 100M;

file_name 확인 후 'RESIZE'를 통해 해당 데이터파일의 크기를 늘릴 수 있습니다.

 

 

-- AUTOEXTEND 활성화
ALTER DATABASE DATAFILE '데이터파일' AUTOEXTEND ON NEXT 자동증가용량;
ALTER DATABASE DATAFILE '데이터파일' AUTOEXTEND ON NEXT 자동증가용량 MAXSIZE 최대설정용량;
-- 실제 쿼리 예시
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf' AUTOEXTEND ON NEXT 100M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 500M;

또는 'AUTOEXTEND ON'을 통해 데이터 파일의 용량이 자동으로 증가되도록 활성화할 수 있습니다.

 

 

-- DATAFILE 추가
ALTER TABLESPACE '테이블스페이스명' ADD DATAFILE '추가할 데이터파일' SIZE 파일용량;
-- 실제 쿼리 예시
ALTER TABLESPACE USERS ADD DATAFILE '/u01/app/oracle/oradata/XE/users1.dbf' SIZE 100M;

-- DATAFILE 추가 및 자동 확장 설정
ALTER TABLESPACE '테이블스페이스명' ADD DATAFILE '추가할 데이터파일' SIZE 파일용량 AUTOEXTEND ON NEXT 자동증가용량 MAXSIZE 최대설정용량;
-- 실제 쿼리 예시
ALTER TABLESPACE USERS ADD DATAFILE '/u01/app/oracle/oradata/XE/users2.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1G;

다음 쿼리와 같이 해당 테이블스페이스에 새로운 DATAFILE을 추가할 수도 있는데요.

대용량 데이터베이스의 경우에는 tablespace의 용량을 확장하는 것보다 데이터 파일을 추가하여 다수의 파일로 데이터를 관리하는 것이 더 효율적입니다.

 

* 주의할 부분으로는 위 작업 전 남은 디스크 용량을 확인하여 사용 가능한 용량 한도 내에서 작업을 수행해야 합니다.