ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_655536183_298 by 128 in tablespace SYSAUX

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

에러 : ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_655536183_298 by 128 in tablespace SYSAUX

alert log에 아래 에러 발생

Wed Jan 17 09:00:37 2019
ORA-1654: unable to extend index SYS.WRH$_SQL_PLAN_PK by 128 in tablespace               SYSAUX
Thu Jan 17 09:31:13 2019
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_655536183_298 by 128 in                 tablespace SYSAUX
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 2097152 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
 select total_size,awr_flush_emergency_count from v$ash_info;

SYSAUX 테이블 스페이스 용량 확인 97%사용중임을 확인

SQL> 
col "TOTAL SIZE" format 999,999,999,999
col "USED SIZE" format 999,999,999,999
col "FREE SIZE" format 999,999,999,999
col "MAX FREE" format 999,999,999,999
col "TS Name" for a20
col "Used rate" for a10
set  trimspool on newpage 1 linesize 120 pages 1000 heading on underline on echo off
 
select t.tn  "TS Name", 
       round(t.sizes,2) "TOTAL SIZE", 
       (round(t.sizes,2) - round(f.sizes,2))"USED SIZE",
       round(f.sizes,2) "FREE SIZE", 
       round(f.msizes,2) "Max Free", 
       round(((t.sizes - f.sizes) /t.sizes) * 100,2)||'%' "Used rate"
 from ( select tablespace_name tn, SUM(bytes) Sizes 
        from dba_data_files 
        group by tablespace_name) t, 
      (select tablespace_name tn, SUM(bytes) sizes, 
              MAX(bytes) msizes 
       from dba_free_space 
       group by tablespace_name) f 
      WHERE t.tn=f.tn 
      AND t.tn = 'SYSAUX'
      order by t.tn;
 
TS Name            TOTAL SIZE         USED SIZE          FREE SIZE     Max Free Used rate
-------------------- ---------------- ---------------- ---------------- ---------------- ----------
SYSAUX              732,954,624       716,570,624         16,384,000      720,896 97.76%

해결 방법 : SYSAUX 테이블스페이스 용량을 늘려준다. 또는 스냅샷 삭제

SYSAUX 테이블스페이스 용량 늘려주는 방법

datafile 추가

SQL> alter tablespace SYSAUX add datafile '/oracle/app/oracle/oradata/ORCL/sysaux02.dbf' size 100m;

또는 

datafile resize

SQL> alter database datafile '/oracle/app/oracle/oradata/ORCL/sysaux01.dbf' resize 1024m;

스냅샷 삭제 방법

snapshot 갯수 확인

SQL> select snap_id, begin_interval_time from sys.dba_hist_snapshot order by snap_id;
 
   SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
      1459 09-JAN-19 12.00.28.486 AM
      1459 09-JAN-19 12.00.28.938 AM
      .
      .
      1660 17-JAN-19 08.00.31.937 AM
 
394 rows selected.

스냅샷 삭제(필요한 스냅샷 남기고 삭제)

나의 경우 모두 삭제함

SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 1459, high_snap_id=> 1660 );

스냅샷을 삭제한 뒤 사용가능 한 용량은 생겼을 것임

하지만 용량 조회를 해보면 그대로 사용중으로 나오는데 이건 HWM 때문에 그런것 자세한 내용은

https://positivemh.tistory.com/350 오라클 reorg, hwm, shrink, move 설명 및 테스트 글을 참조하면됨

아래 내용은 SYSAUX가 매우 크기 않다면 굳이 해주지 않아도 DB를 사용하는데 문제가 없음

HWM을 줄여주기 위해 SHIRINK 작업 진행

SYSAUX에 있는 TABLE ROW MOVEMENT ENABLE 설정

쿼리 결과로 나온 내용 붙여넣기

SQL> 
set lines 200
col segment_name for a20
select 'alter table '||segment_name||' ENABLE ROW MOVEMENT;'
from dba_segments
where tablespace_name='SYSAUX'
and segment_type='TABLE';

SYSAUX에 있는 TABLE SHRINK 실행

쿼리 결과로 나온 내용 붙여넣기

SQL> 
select 'alter table '||segment_name||' SHRINK SPACE;'
from dba_segments
where tablespace_name='SYSAUX'
and segment_type='TABLE';

SYSAUX에 있는 TABLE ROW MOVEMENT DISABLE 설정

쿼리 결과로 나온 내용 붙여넣기

SQL>
set lines 200
col segment_name for a20
select 'alter table '||segment_name||' DISABLE ROW MOVEMENT;'
from dba_segments
where tablespace_name='SYSAUX'
and segment_type='TABLE';

용량확인

SQL> select t.tn  "TS Name", 
       round(t.sizes,2) "TOTAL SIZE", 
       (round(t.sizes,2) - round(f.sizes,2))"USED SIZE",
       round(f.sizes,2) "FREE SIZE", 
       round(f.msizes,2) "Max Free", 
       round(((t.sizes - f.sizes) /t.sizes) * 100,2)||'%' "Used rate"
 from ( select tablespace_name tn, SUM(bytes) Sizes 
        from dba_data_files 
        group by tablespace_name) t, 
      (select tablespace_name tn, SUM(bytes) sizes, 
              MAX(bytes) msizes 
       from dba_free_space 
       group by tablespace_name) f 
      WHERE t.tn=f.tn 
      AND t.tn = 'SYSAUX'
      order by t.tn;
 
TS Name            TOTAL SIZE         USED SIZE          FREE SIZE     Max Free Used rate
-------------------- ---------------- ---------------- ---------------- ---------------- ----------
SYSAUX              732,954,624       558,825,472        174,129,152        5,242,880 76.24%

원인 : SYSAUX 테이블 스페이스 용량 부족으로 인한 ASH 테이블 내용이 쌓이지 못해서 발생한 에러

참조 : http://promaster.tistory.com/91

https://community.oracle.com/thread/1026972