꿈꾸는 개발자, DBA 커뮤니티 구루비

과거 DBA 가 다음과 같은 사항을 결정해야만 했다.

언두 세그먼트 헤더를 두고 벌이는 경합을 줄이기 위해 필요한 롤백 세그먼트의 수
각 롤백 세그먼트에 할당해야 할 익스텐트의 수
익스텐트의 크기
공간 재활용을 위한 롤백 세그먼트의 되돌림 속도

그러나, 9i부터는 이렇게 사항들을 결정할 필요가 없다.

DBA가 해야 하는 것이라고는 언두 데이터가 유지되어야 하는 시간(초)으로 UNDO_RETENTION init.ora 매개변수를 설정하고 UNDO 테이블 영역이 사용하는 공간을 모니터링 하는 것뿐이다.


\- Undo Usage Check
select s.username, s.sid, s.serial#, rn.name,     rs.curext
,rs.curblk,  t.used_ublk, t.used_urec
from    v$transaction   t
,v$session       s
,v$rollname      rn
,v$rollstat      rs
where  t.addr     = s.taddr
and    t.xidusn   = rn.usn
and    rn.usn     = rs.usn

"여기에 설정된 언두 유지 기간은 필수 사항인가 아니면 권고 사항인가?"라고 질문할 수도 있다.
물론 권고 사항이다. 충분한 공간을 가지고 있거나 언두 세그먼트를 확장함으로써 충분한 공간을 확보할 수 있다면 오라클은 만료 전에 언두 정보를 재사용하지 않는다.
한편 유지 기간을 만족시킬 수 있을 만큼의 공간이 없다고 해서 트랜잭션이 실패하지는 않는다. 다시 말해서, 오라클은 필요한 경우 커밋이 완료된 트랜잭션에 의해 생성된 언두 정보를 기한 전에 만료시킨다.

Undo 사용에 대한 체크 : 오라클은 필요한 경우 커밋이 완료된 트랜잭션에 의해 생성된 언두 정보를 기한 전에 만료시킨다.


sys@ARTDOM> create undo tablespace undo_big datafile size 1m
autoextend on next 1m maxsize 2048M ;
Tablespace created.

sys@ARTDOM> create undo tablespace undo_small datafile size 1m
autoextend off ;
Tablespace created.

sys@ARTDOM>show parameter undo_retention

NAME                                 TYPE        VALUE
\-----------------------------------\- \----------\- \-----------------------------\-
undo_retention                       integer     900

sys@ARTDOM>alter system set undo_tablespace = undo_big scope = memory ;

System altered.

sys@ARTDOM>create table t ( x char(2000), y char(2000), z char(2000));
Table created.

sys@ARTDOM>insert into t values ( 'x','x','x');
1 row created.

sys@ARTDOM>begin
2  for i in 1 .. 500
3  loop
4    update t set x=i, y=i, z=i ;
5    commit ;
6  end loop ;
7  end ;
8  /

PL/SQL procedure successfully completed.

sys@ARTDOM>select bytes, maxbytes from dba_data_files where tablespace_name = 'UNDO_BIG';

BYTES   MAXBYTES
\---------\- \---------\-
5242880 2147483648

\- 1M \--> 5M

sys@ARTDOM>alter system set undo_tablespace = undo_small scope = memory ;

System altered.

sys@ARTDOM>show parameter undo_tablespace

NAME                                 TYPE        VALUE
\-----------------------------------\- \----------\- \-----------------------------\-
undo_tablespace                      string      UNDO_SMALL

sys@ARTDOM>drop table t ;

Table dropped.

sys@ARTDOM>create table t ( x char(2000), y char(2000), z char(2000)) ;

Table created.

sys@ARTDOM>insert into t values ( 'x','x','x');

1 row created.
sys@ARTDOM>begin
  for i in 1 .. 500
  loop
   update t set x=i,y=i,z=i ;
   commit ;
  end loop ;
  end ;
  /

PL/SQL procedure successfully completed.

sys@ARTDOM>select bytes,maxbytes from dba_data_files where tablespace_name ='UNDO_SMALL';

BYTES   MAXBYTES
\---------\- \---------\-
1048576          0

결론. 오라클은 Undo 데이터 파일을 확장하지 않았지만(확장하도록 허용되지 않았지만), 그렇다고 트잰잭션이 수행되지 않은 것도 아니다.
오히려 오라클은 전혀 롤백이 필요 없는 언두 정보를 사전에 말료시켰다.

UNDO 테이블 영역에 대한 주의사항

지정가능한 것
데이터 파일의 최대 크기를 제한하거나 데이터 파일의 자동 확장 속성을 해제함으로써 UNDO 테이블스페이스 영역의 궁극적인 크기를 지정할 수 있다.
UNDO 테이블스페이스 영역의 지원에 사용되는 데이터 파일의 이름을 지정할 수 있다.
현행 UNDO 테이블 영역의 세그먼트 확장 알고리즘은 다른 세그먼트의 만료된 익스텐트를 재사용하기 전에 롤백 세그먼트를 키운다. 이러한 이유 때문에 프로덕션 환경에서는 UNDO 테이블 영역에 자동 확장 데이터 화일을 사용하지 않도록 하여야 한다.

UNDO SIZE 줄이는 방법

1. 새로운 UNDO 테이블 영역을 생성한다. 예를 들면


CREATE UNDO TABLESPACE UNDO_NEW DATAFILE SIZE <N>

2. 이 UNDO 테이블 영역을 사용한다. 예를 들면


ALTER SYSTEM SET UNDO_TABLESPACE=UNDO_NEW SCOPE=BOTH

3. 이전의 UNDO 테이블 영역을 삭제한다. "ORA-30013 : undo tablespace is currently in use" 메시지가 나타날 수도 있다.
이 메시지는 여전히 이 UNDO 테이블 영역을 필요로 하는 ( 즉, 여전히 이 테이블 영역을 이용하고 있는 ) 트랜잭션이 존재함을 의미한다.
이 경우에는 삭제하기 전에 얼마동안 기다려야 한다. 예를 들면


DROP TABLESPACE OLD_UNDO TABLESPACE