ora-01562 failed to extend to rollback segment
oracle system계정으로 log-in후 작업
1. 트랜잭션에 불충분한 공간 / ora-1562, ora1560
- 테이블 스페이스 내의 데이타 파일의 확장
- 데이타 파일의 autoextend 허가
- alter database DATAFILE '' autoextend on
- 테이블 스페이스에 파일 추가
- alter tablespace rbs add datafile 'D:\Oracle\ORADATA\swdbini\RBS02.DBF' size 300M autoextend on next 10m maxsize unlimited;
=> 가능한 unlimited는 사용하지 않는것이 좋다
- maxextents 변경
- alter rollback segment rbs01(rollback_segment name)
storage ( maxextents 200) ;
- deallocating space from rollback segments
- alter rollback segment rbs01(rollback_segment name)
shrink to 4m ;
cf) 4m는 deallocating후 값 할당 = default = optimal에 설정된 값
- if extents are active, may not shrink to the requested size
- if integer is not specified, the oracle server attempts to deallocate extents
until the size of the rollback segment is equal to OPTIMAL
- if the integer specified is larger than the current size of the rollback segment,
this command is ignored.
- resize
alter database datafile 'D:\Oracle\ORADATA\swdbini\RBS02.DBF' RESIZE 500M;
2. 세그먼트가 maxextents에 도달했을때 / ora-1628
- 롤백 세그먼테에 대한 maxextents 증가
- 롤백 세그먼트를 삭제하고 더 큰 크기의 extend를 갖는 롤백 세그먼트 재생성하여 문제 재발 방지
############# tablespace status
1. 현재 ROLLBACK SEGMENT가 포함되어 있는 RBS TABLESPACE의 크기 확인
SELECT file_name, bytes
FROM dba_data_files
WHERE tablespace_name = 'RBS';
2. 각 TABLESPACE별 FREE SPACE는 다음과 같은 방법으로 확인
SELECT tablesapce_name, sum(bytes), max(bytes)
FROM dba_free_space
GROUP BY tablespace_name;
3. 각 ROLLBACK SEGMENT의 SIZE및 발생한 EXTENT의 수 확인
SELECT segment_name, initial_extent, next_extent, max_extents, rssize, extents, xacts
FROM dba_rollback_segs a, v$rollstat b
WHERE a.segment_id = b.usn;
(1) INITIAL_EXTENT는 ROLLBACK SEGMENT가 필요한 경우 처음으로 자신의 SPACE를 잡는크기이며,
이 크기만큼의 연속된 공간을 RBS TABLESPACE로 부터 잡는다.
(2) NEXT_EXTENT는 INITIAL_EXTENT를 잡은 후에 추가적으로 SPACE가 필요한 경우 이 크기만큼씩
SPACE를 잡게 된다.
(3) MAXEXTENTS는 각 ROLLBACK SEGMENT에 지정된 최대 발생가능한 EXTENT의 갯수이다. 여기에
설정된 값 이상의 EXTENT를 발생시킬 수 없다.
(4) RSSIZE는 현재 각 ROLLBACK SEGMENT가 잡고 있는 RBS TABLESPACE내의 ROLLBACK SEGMENT의
크기이다.
(5) EXTENTS는 현재 각 ROLLBACK SEGMENT별로 발생한 EXTENT의 갯수이다.
(6) XACTS는 현재 각 ROLLBACK SEGMENT를 잡고 있는 ACTIVE ROLLBACK TRANSACTION의 갯수이다.
ROLLBACK SEGMENT를 OFFLINE하거나 DROP할 때는 이 XACTS가 0인지 확인하고 작업하여야 한다.
SEGMENT INITIAL NEXT MAX RSSIZE EXNTENTS XACTS
--------- -------- -------- -------- --------- ---------- --------
SYSTEM 262144 262144 121 407552 8 0
R01 262144 262144 121 530432 2 0
R02 262144 262144 121 3192832 12 0
R03 262144 262144 121 5056512 19 0
R04 262144 262144 121 11180032 42 1
1) ORA-1562발생시 표시된 message에 나타난 ROLLBACK SEGMENT이름의
EXTETNS가 MAX_EXTETNS의 수와 같으면, 이것은 그 ROLLBACK SEGMENT가
MAXEXTENT에 도달하여 이 오류가 발생한 것
2) 그렇지 않은 경우는 TRANSACTION에 필요한 SPACE가 더 이상 RBS
TABLESPACE에 FREE SPACE로 남아 있는 것이 없어서 발생한 것 - 테이블 스페이스 파일 추가 및 autoextend 허가
-- 오류가 발생한 ROLLBACK SEGMENT의 RSSIZE보다 큰 RSSIZE를
가지는 ROLLBACK SEGMENT가 존재하여 이 TRANSACTION을 수행하기에 충분하다고 판단되는
경우 그 ROLLBACK SEGMENT를 지정하여 사용할 수 있다.
RSSIZE가 매우 큰 ROLLBACK SEGMENT가 R04라고 할때, SQLPLUS 상에서는 다음과 같이 하면
된다.
SET TRANSACTION USE ROLLBACK SEGMENT r04;
### alert 파일 ora-1562의 trace분석
1. FULL이 해제되는 경우
(1) FULL status를 가진 rollback segment 내의 모든 transaction들이
commit되는 경우
예를 들어, sqlplus 상에서 ORA-1562를 만난 sql문 수행 문 위에
commit 문장을 수행하거나, 오류를 만난 program을 완전히 종료하는 경우들이
해당된다.
이렇게 되면 v$rollstat의 xacts (active transactions)값이 0이 된다.
(2) 해당 rollback segment를 OFFLINE혹은 ONLINE으로 변경하는 경우
v$rollstat의 xacts값이 1이상일 때 OFFLINE하게 되면 status가 OFFLINE
PENDING상태가 되고, 모든 transaction이 완전히 rollback이 되거나
commit되어 clear되어 xacts가 0가 되면 OFFLINE PENDING이 OFFLINE 상태로
변경된다.
XACTS가 1이상일 때 OFFLINE시켜 OFFLINE PENDING이 된상태에서 다시
alter rollback segment문장을 이용해 ONLINE시키면 이제는 FULL이 아닌
ONLINE상태로 status가 변경된다. 그러나 여전히 이 rollback segment를
차지한 모든 transaction들이 commit이 되지 않았다면 이 rollback
segment를 사용하려는 시도는 실패하고 다른 rollback segment를
사용하게 된다.
rollback segment (예를 들어 r01) 를 online이나 offline으로 변경시키는
명령은 다음과 같다.
alter rollback segment r01 offline;
alter rollback segment r01 online;
2. ORA-1562를 만난 transaction을 성공적으로 수행하기 위한 방법
(1) RBS를 확장하는 방법
가장 간단하게는 RBS tablespace의 크기를 늘려 rollback segment가
충분히 extend할 수 있도록 하는 것이다. RBS의 datafile을 늘리거나 (7.2
이상), 새로운 datafile을 하나 더 추가할 수 있다.
- size를 50m에서 100m로 증가시키는 경우
alter database datafile '/mnt3/rctest73/server/oradata/rbs01.dbf'
resize 100m;
- 50m 짜리 datafile을 하나 더 추가하는 경우
alter tablespace rbs add datafile '/mnt3/rctest73/server/oradata/rbs02.dbf' size 50m;
(2) RBS tablespace를 확장하지 않고 해결하는 방법
다음 문장을 수행하여 보아 FULL이 아닌 충분한 크기가 확보된 rollback
segment가 존재한다면 그 rollback segment를 지정하여 오류가 발생한
transaction을 재수행할 수 있다.
select usn, rssize from v$rollstat;
select * from v$rollstat;
여기에서 나타난 rssize값이 확보된 rollback segment의 영역이다.
첫번째로는 이 중 일부분의 rollback segment를 drop 후 다시 create하거나
shrink시켜 free space를 확보하여 다른 rollback segment가 extend
가능하도록 할 수 있으며, 두번째 방법으로는 이 중 rssize가 FULL된
rollback segment보다 충분히 큰 rollback segment를 지정하여
transaction을 수행시킬 수 있다.
각각의 syntax는 다음과 같다(예를 들어 r01).
- rollback segment drop 방법
alter rollback segment r01 offline;
drop rollback segment r01;
- rollback segment shrink 방법
alter rollback segment r01
- rollback segment를 지정하여 사용하는 방법
(r02의 rssize가 충분히 크고 FULL이 아니라면)
set transaction use rollback segment r02;
### 이러한 ROLLBACK SEGMENT에 관한 오류는 ROLLBACK SEGMENT가 일단 필요한 SPACE를 확보한
이후에는 그 크기가 줄어들지 않는 것이 그 원인이 될 수도 있는데 이때 ROLLBACK
SEGMENT에 OPTIMAL SIZE를 지정하면 어느 정도의 해결이 가능하다.
OPTIMAL을 지정하면 ROLLBACK SEGMENT가 그 크기 이상으로 증가되는 경우, 이후 OPTIMAL로
지정된 크기만 유지하도록 ROLLBACK SEGMENT가 줄어들게 된다.
이때, 이값을 너무 작게 잡으면 빈번하게 ROLLBACK SEGMENT가 늘어나고 줄어드는 작업으로
인해 PERFORMANCE에 지장을 초래할 수 있으므로 20~30개의 EXTENT정도를 보유할 수 있도록
한다.
OPTIMAL의 지정 방법은 ROLLBACK SEGMENT생성시나 그 이후에 다음과 같이 하면 된다.
CREATE ROLLBACK SEGMENT r01
TABLESPACE rbs
STORAGE (INITIAL 1M NEXT 1M OPTIMAL 20M) ;
이미 생성된 rollback segment에 대해서는,
ALTER ROLLBACK SEGMENT r01 STORAGE(OPTIMAL 20M);