[Oracle] ORA-01653 : TableSpace에서 확장 할 수 없습니다.

테이블 스페이스가 부족해서 데이터가 더이상 들어갈 수 없을때 발생하는 에러

일단, 테이블스페이스의 이름과 해당 데이터파일의 사용량을 조회해보자.

 

 

1. 테이블스페이스 조회

 

1)  해당 테이블의  테이블 스페이스 조회

select * from dba_tablespaces ;

select table_name,tablespace_name
  from dba_tables
 where owner='스키마명' and table_name='테이블명'

 

2) 테이블 스페이스(논리)의   데이터 파일(물리) 확인

select tablespace_name, file_name, bytes, autoextensible
  from dba_data_files
 where tablespace_name='테이블 스페이스 명';

3) 전체 출력 (가독성있게)

SELECT    A.TABLESPACE_NAME "테이블스페이스명",
          A.FILE_NAME "파일경로",
           (A.BYTES - B.FREE)    "사용공간",
            B.FREE                 "여유 공간",
            A.BYTES                "총크기",
            TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"
      FROM
       (
         SELECT FILE_ID,
                TABLESPACE_NAME,
                FILE_NAME,
                SUBSTR(FILE_NAME,1,200) FILE_NM,
                SUM(BYTES) BYTES
           FROM DBA_DATA_FILES
         GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)
       ) A,
       (
         SELECT TABLESPACE_NAME,
                FILE_ID,
                SUM(NVL(BYTES,0)) FREE
           FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME,FILE_ID
       ) B
      WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
         AND A.FILE_ID = B.FILE_ID;

 

위 SQL로 현재 스키마에서 사용하는 테이블스페이스와 데이터파일의 크기등을 확인 할 수 있다.

데이터 파일이 꽉 차 있거나, 서버의 disk가 부족할 수 있다. 

만약 서버디스크 용량과 상관없이 TableSpace의 물리, 논리적인 공간문제인 경우 아래와 같이 해결하자!

 

 

 

 

2. 해결 방법

 

1. TableSpace의 AutoExtend를 값을 on으로 변경한다.

2. TableSpace의 Datafile을 증가/추가 한다. 

3. Table별 데이타 용량을 체크하여, 불필요한 데이터를 삭제한다.

 

1,2번은 TableSpace의 논리적인 설정값과 DataFile공간을 넓이는 방법이지만,

서버내 disk 용량에 대한 물리적인 하드의 문제인 경우, 3번방법으로 해결해야 한다. 

 

 

1) TableSpace의 Datafile을 증가 / 추가 한다.

1.1 기존의 데이터파일의 사이즈를 늘려준다.

alter database datafile '/data/kkk/kkk01.dbf/ resize 10G;

 

 

또는

 

1.2 데이터 파일을 추가한다.

alter tablespace app_data ADD DATAFILE '/data/kkk/kkk02.dbf' size 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

 

 

 

2) TableSpace의 AutoExtend를 NO일 경우, YES로 변경한다.

 

1. TableSpace가 autoextend가 no/off되어 있는지 확인한다. (system 계정 사용)

select    file_name, tablespace_name, bytes, autoextensible
from     dba_data_files
where    tablespace_name='KKK';

2. 테이블 스페이스를 자동으로 사이즈가 늘어날 수 있도록 autoextend를 on으로 설정한다.

alter database datafile '/data/kkk/kkk01.dbf' autoextend on next 1M

alter tablespace kkk alter datafile 'kkk' autoextend on maxsize 20M;

 

 

3) Table별 데이타 용량을 체크하여 불필요한 데이타를 삭제한다.

 

위 사항으로 해결되지않는다면,

DB 서버 내 disk 용량을 확인해보아야 한다.

Table별 사용량을 조회하여,

불필요한 데이터를  purge (영구삭제) 한다 

-- 데이터 삭제후 휴지통 비우기
purge recyclebin;

 

영구삭제 / 데이블 복원 참고글 : 2020.11.25 - [일/DataBase] - [Oracle] 삭제된 테이블 복구

 

 

 

참고 : https://goddaehee.tistory.com/43 [갓대희의 작은공간]