Neo-Oracle Home

언제 Conventional Data Load를 사용해야 하나?

개요

http://blog.oracle48.nl/oracle-database-undo-space-explained/

만약 active undo extents만 존재하여 더이상 해당 트랜잭션에 대한 undo 공간을 확보할 수 없을 경우 ORA-30036 unable to extend segment in Undo tablespace 에러를 받게 되고 자동으로 트랜잭션은 rollback되게 된다.

이 현상이 얼마나 일어났는지 확인은 아래 sql을 통해 가능하다.

만약 active상태의 extents이 필요한데 expired extents이 없다면 unexpired(undo_retention 기간이 아직 지나지 않은 상태) extents를 빼앗게(steal) 되는데 해당 count를 확인하는 법은 아래와 같다.

※ steal 못하게 하려면 undo 테이블스페이스에 대해 retention guarantee 옵션을 주면 된다.

단.. 이러면 이제 unexpired extent를 뺏지 않기 때문에 조금 긴 dml의 경우 실패할 경우가 자주 생길 것이다. 이는 즉 ORA-30036과 ORA-01555 중 어느것을 선택하느냐이다.

만약 이렇게 steal된 unexpired extents에 대해 read consisteny read 수행하면 ORA-01555 snapshot too old 발생하고 이 역시 v$undostat에서 확인 가능하다.

※ undo extent 크기와 퍼센트를 확인하는 쿼리. 단 free는 포함되지 않음

■ undo size tune

1. 기본문. 예외처리가 없음(20)
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
begin
        select 1
        into dummy
        from em
        where department_id = v_department_id;
        dbms_output.put_line('ok');
end;
/

2. 사전정의된 예외처리방법(20)
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
begin
        select 1
        into dummy
        from em
        where department_id = v_department_id;
        dbms_output.put_line('ok');
exception
        when too_many_rows then
          dbms_output.put_line('too many rows return');
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

3. 사전정의안된 오류 예외처리 방법(20). too_many_rows 가 없다고 가정
declare
        v_department_id em.department_id%type := &deptno; --deptno를 받음
        dummy number;   --의미없지만 select가 돌아가게 하기 위해
       
        toomany exception;                     --1. exception 명 선언
        pragma exception_init(toomany, -1422);  --2.
begin
        select 1
        into dummy  --의미없지만 select가 돌아가게 하기 위해
        from em
        where department_id = v_department_id;
        dbms_output.put_line('ok');
exception
        when toomany then
          dbms_output.put_line('too many rows return');
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

4. 유저 정의 예외처리(300) 에러메시지 발생, 중지
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
begin
        if v_department_id >= 300 then
          raise_application_error(-20000, '300이상의 값을 입력하지 마시옵소서');
        else
          select 1
          into dummy
          from em
          where department_id = v_department_id;
            dbms_output.put_line('ok');
        end if;
exception
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

5. 유저 정의 예외처리(300) 실행 후 에러 메시지 정상 핸들링
declare
        v_department_id em.department_id%type := &deptno;
        dummy number;
        over_dept_err exception;
        pragma exception_init(over_dept_err, -20000);
begin
        if v_department_id >= 300 then
          raise_application_error(-20000, '300이상의 값을 입력하지 마시옵소서');
        else
          select 1
          into dummy
          from em
          where department_id = v_department_id;
            dbms_output.put_line('ok');
        end if;
exception
        when over_dept_err then
          dbms_output.put_line(sqlerrm);
        when others then
          dbms_output.put_line(sqlerrm);
end;
/

이거 말고 더 예제는 있습니다.