[오라클] 테이블스페이스 RESIZE

오늘은, 최근 문제가 되었던 테스트서버 테이블스페이스 RESZIE 관련하여 글을 정리해볼까 합니다.

 

우선 환경은 XE에디션의 오라클DB로, 최대 용량이 12GB입니다.

 

내부 DB를 구성하며, 사용 계정에 DEFAULT TABLESPACE를 지정하지 않아 문제가 되었었습니다.....

 

결국, USERS 테이블스페이스가 DEFAULT TABLESPACE로 지정되었으며, AUTO EXTENDS 옵션이 작동되어 무려 5GB의 용량을 차지하게 되었으며, 제 희망사항은, 해당 테이블 스페이스의 용량을 줄이고, 사용 테이블 스페이스의 용량을 증가시키고 싶었습니다.

 

제가 오늘 작성한 글의 경우 테이블스페이스의 용량을 '줄이는' 케이스이지만, 늘리는 케이스는 더 간단합니다.

(가능한 만큼 RESIZE를 통해 증가 시켜주거나, 데이터 파일을 추가하는 방법)

문제 발생 순서는 다음과 같습니다.

1. 프로그램상에서 백업테이블을 생성.

2. "ORA-12954: 요청이 허용되는 최대 데이터베이스 크기인 12GB를 초과합니다." 오류 발생

3. USERS 테이블스페이스에 있는 백업 테이블 삭제(테스트 데이터라 삭제 가능)

4. 이 후, RESIZE 시도

5. "ORA-03297: 파일에 요청된 RESIZE 값을 넘어선 사용된 값을 포함하고 있습니다" 오류 발생

 

이 후, 해결 순서는 다음과 같습니다.

0. DEFAULT TABLESPACE 변경

ALTER USER USER1 DEFAULT TABLESPACE AFTER_TABLESPACE;

 

1. 테이블스페이스 별 사용량 및 여유 공간 확인

select   substr(a.tablespace_name,1,30) tablespace,
round(sum(a.total1)/1024/1024,1) AS "TotalMB",
round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
round(sum(a.sum1)/1024/1024,1) "FreeMB",
round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"
from
(select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
from     dba_free_space
group by tablespace_name
union
select   tablespace_name,sum(bytes) total1,0,0,0
from     dba_data_files
group by tablespace_name) a
group by a.tablespace_name

 

2. 테이블스페이스 별 데이터파일 확인

SELECT *
from dba_data_files

 

3. FILE_ID 컬럼을 통해, 테이블스페이스 세부 검색

SELECT *
from dba_data_files
where file_id = 7;--검색 할 파일 ID

 

4. 테이블스페이스의 세그먼트 별 용량 확인

SELECT owner, segment_name, segment_type, sum(bytes)/1024/1024 as MB
FROM dba_segments
WHERE segment_type='TABLE' -- 확인 할 세그먼트의 타입
GROUP BY owner, segment_name, segment_type
ORDER BY MB desc;

 

5. 이 후, 불필요 데이터를 확인

  - 저의 경우 BIN$XXX 즉, 리사이클빈 데이터가 많아, 해당 데이터를 삭제하였습니다.

    = 이 경우 복구가 불가하오니 확인 후 삭제해야 합니다.

--테이블 지정하여 삭제
PURGE TABLE "BIN$xxxxxx";

--테이블 스페이스 지정하여 삭제
PURGE TABLESPACE USERS;

--데이터베이스 전체의 Recycle Bin 삭제
PURGE DBA_RECYCLEBIN;

--RECYCLE BIN 옵션 끄기
--현재 세션에 한하여 옵션 끄기 
ALTER SESSION SET recyclebin = OFF;
--시스템 자체의 옵션 끄기 
ALTER SYSTEM SET recyclebin = OFF;

 

6. 기본테이블스페이스 변경

-- USER1의 DEFAULT TABLESPACE를 AFTER_TABLESPACE로 변경
ALTER USER USER1 DEFAULT TABLESPACE AFTER_TABLESPACE;

 

7. 테이블스페이스 리사이징

-- USERS 데이터파일을 1GB로 리사이징
ALTER DATABASE DATAFILE 'C:\DB\MYDB\DATAFILES\USERS01.DBF' RESIZE 1G;

 

순으로 해결하였습니다.

 

사실 사이즈가 부족해서 증가시키는 경우는 은근 있다고 해도, 사이즈를 줄이는 경우는 많지 않을텐데요,

혹 저같이... 실수한 경우 쓰면 괜찮을 것 같습니다.