[ORACLE] ORA-01653 : unable to extend table ... TABLESPACE ...

[ORACLE] ORA-01653 : unable to extend table ... TABLESPACE ...

오라클 DB에 데이터를 INSERT 하다보면 제목과 같은 에러가 뜨면서 INSERT가 안될 때가 있다.
이런경우 TABLE DDL SCRIPT에 TABLESPACE를 명시하지 않아 DB TABLE SPACE를 DEFAULT로 잡아서 SYSTEM.DBF 에 저장되는 경우가 흔한데, 테이블을 용량이 많은곳으로 바꿔주던가, 아니면 현재 들어있는 TABLESPACE 용량을 늘려줘야 한다.

---

TABLESPACE는 1개 이상의 DATAFILE을 갖고있다.
TABLESPACE는 영역이고, 실제 영역안의 사이즈는 DATAFILE(*.DBF)이 사용중인 것이다. 그래서 DATAFILE의 사이즈를 늘려줘야 한다.

각 TABLESPACE의 용량 확인

SELECT A.TABLESPACE_NAME AS "TABLE_SPACE_NAME"
     , A.BYTES/1024/1024  AS "TOTAL_MB"
     , (A.BYTES - NVL(B.FREEBYTES, 0))/1024/1024 AS "USE_MB"
     , NVL(B.FREEBYTES, 0)/1024/1024 AS "FREE_MB"
     , CONCAT(ROUND(NVL(B.FREEBYTES, 0) * 100 / A.BYTES, 2), '%') AS "USAGE(%)"
FROM (
      SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
        FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME
      ) A
   , (
      SELECT TABLESPACE_NAME, SUM(NVL(BYTES,0)) FREEBYTES
        FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME
      ) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY "USAGE(%)" DESC

위 쿼리를 날리면, 각 TABLESPACE들의 토탈용량과, 사용량, 잔여량을 볼 수 있다.
TOTAL_MB, USE_MB, FREE_MB의 현재 MB 단위로 세팅 해 놓은 것이고 값을 각 1024로 더 나누면 GB 단위로 볼 수 있다.

TABLESPACE 용량 늘리기

1. TABLESPACE의 DATAFILE 경로 조회

SELECT TABLESPACE_NAME, BYTES/1024/1024 AS MB, FILE_NAME
FROM DBA_DATA_FILES

위 쿼리를 날리면 이런식으로 나온다.

TABLESPACE_NAME	MB	FILE_NAME
SYSTEM	500	D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
TS1	900	D:\ORACLEXE\APP\ORACLE\ORADATA\XE\TS1.DBF

여기서 늘릴 TABLESPACE_NAME과 FILE_NAME (경로)명을 가지고

2. DATAFILE 수정 (수동으로 용량 늘리기)

만약 SYSTEM DATAFILE의 크기를 500MB -> 2000MB로 늘린다고 하면

ALTER DATABASE DATAFILE 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' RESIZE 2000M

RESIZE 2000M 시, 500MB + 2000MB = 2,500MB가 아니라 2000MB로 고정된다. 즉 1,500MB가 증가됨.

3. DATAFILE 자동 용량 증가

DATAFILE의 사이즈가 다 찰 경우 자동으로 늘려주는 옵션이 있다.

-- 그냥 계속 증가함.
ALTER DATABASE DATAFILE 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' AUTOEXTEND ON

-- 자동으로 30MB씩 늘어나며, 2000MB가 되면 더이상 늘어나지 않음
ALTER DATABASE DATAFILE 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' AUTOEXTEND ON NEXT 30M MAXSIZE 2000M

-- 자동으로 30MB씩 늘어나며, 허용수치까지 계속 늘어남.
ALTER DATABASE DATAFILE 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED

DATAFILE은 ORACLE 32BIT에서 16GB, 64BIT에서 32GB까지 가능함.

4. DATAFILE들의 AUTO EXTEND 여부 확인

SELECT TABLESPACE_NAME, BYTES/1024/1024 AS MB, FILE_NAME, AUTOEXTENSIBLE FROM DBA_DATA_FILES

AUTOEXTENSIBLE 컬럼 값이 YES면 자동증가, NO면 자동 증가하지 않는다.