[Oracle][ORA-01555] snap shot too old 에러

ORA-01555 snap shot too old 에러란?

drop table mcustsum purge;

create table mcustsum as
select rownum custno,
       '2025' || lpad(ceil(rownum / 100000), 2, '0') salemm,
       decode(mod(rownum, 12), 1, 'A', 'B') salegb,
       round(dbms_random.value(1000, 100000), -2) saleamt
from dual
connect by level <= 120000;

💡 scott A 세션이 select를 했는데 15분 넘게 select를 하고 있는 중입니다. 그런데 그 사이에 scott B 세션이 mucustsum의 120만번째 데이터인 salegb를 B에 K로 변경했습니다. 그러면 B는 UNDO segment에 들어가고 scott A는 update 보다 먼저 한 select문이고 select하기 직전에 최종적으로 commit 된 데이터는 B이기 때문에 B를 봐야합니다.

그런데 15분이 지났고 update 문은 commit을 했기 때문에 B가 있던 undo segment 자리를 다른 트랜잭션에서 의해서 덮어써질 합당한 이유가 생겼습니다. 그래서 덮어져버리면 scott A 세션은 120만번째 데이터를 볼 때 에러가 나고 select가 실패하게 되는 그 에러가 바로 ORA-01555 snap shot too old 에러입니다.

---

오라클에서 권장하는 해결방법

오라클에서 가장 길게 도는 쿼리문의 시간에 맞춰서 undo_retention을 지정하라고 권장하고 있음

---

실습1. ORA-01555 snap shot too old 에러를 경험하세요

실습.  ORA-01555  snap shot too old 에러를 일으켜 보시오 !

  #1.  10m 짜리 언두 테이블 스페이스를 생성합니다.

      create undo tablespace small_undo
      datafile '/home/oracle/small_undo.dbf' size 10m autoextend off;
      
      
 SYS @ ORA19 > show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

 #2. 10m 언두 테이블 스페이스를 현재 사용중인 undo tablespace 로 변경합니다.
      
      alter system set undo_tablespace=small_undo;
      
 SYS @ ORA19 >  show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      SMALL_UNDO
SYS @ ORA19 >

      
 #3. 테이블을 생성하고 데이터를 10000 건을 입력합니다.
     
      create table t1(c1 int, c2 char(300));
      
      insert /*+ append */ into t1
      select level, 'dummy'
      from dual
      connect by level <= 10000;
      
      commit;
      
  #4. 인덱스를 생성합니다. 

      create index t1_n1 on t1(c1);
      
  #5. 사용자 정의 함수를 생성합니다.
      
      create or replace function fsleep(v1 int, vsleep int)
      return number
      is
      begin
        dbms_lock.sleep(vsleep);
      
      return 1;
      end;
      /
      
  # 6. 버퍼 캐쉬 사이즈를 1m 로 변경합니다.
      
      alter system set db_cache_size = 1m;
      
      
   #7. 위와 같이 환경구성을 하고 아래의 쿼리를 수행한다.
      
      
      select /*+ index(t1) */ 
      c1, substr(c2,1,10)
      from t1
      where fsleep(c1, 0.01) = 1 
      and c1 > 0;
      


-- session 2 -------
      
# 1. 현재 언두 세그먼트가 뭐가있는지 확인합니다.

      -- get rollback segment name

      col rollback_seg new_value v_rollback_seg
      
      select '_SYSSMU'||max(segment_id)||'$' as rollback_seg
      from dba_rollback_segs
      where segment_name <> 'SYSTEM';
      
      -- do very frequent commit on t1

#2. t1 테이블에 update 를 계속 반복 

      begin
      for idx in 1 .. 1000 loop
      for idx2 in 1 .. 10000 loop
      set transaction use rollback segment "&v_rollback_seg";
      update t1 set c2 = 'dummy'||idx
      where c1 = idx2;
      commit;
      end loop;
      end loop;
      end;
      /

---

문제1. 위의 상황을 해결하기위해서 첫번째 현재 database에서 사용할 undo tablespace 를 원래 undo tablespace 인 undotbs1 로 되돌리시오

SYS @ ORA19 > alter system set undo_tablespace=undotbs1;

시스템이 변경되었습니다.

SYS @ ORA19 > show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1
SYS @ ORA19 >

---

문제2. 데이터 베이스 버퍼 캐쉬 사이즈를 결정하는 파라미터인 db_cache_size 를 50m 로 변경하시오

SYS @ ORA19 > alter system set db_cache_size=50m;

시스템이 변경되었습니다.

---

[공간정리]

owi_tbs 테이블스페이스 삭제

SYS @ ORA19 > drop tablespace owi_tbs including contents and datafiles cascade constraints;