오라클 UNDO 상태를 확인하는 SQL문 · 게시판

UNDO TABLESPACE 상태를 확인하는 SQL입니다.

set linesize 150

col BeginTime for a16

col EndTime for a16

col Active(%) for a10

col UnExp(%) for a10

col Exp(%) for a10

col Usage(%) for a10



select to_char(t.BEGIN_TIME, 'YYMMDD HH24:MI:SS') "BeginTime",

       to_char(t.END_TIME, 'YYMMDD HH24:MI:SS') "EndTime",

       t.TXNCOUNT,

       b.x_undosize "TBS_SIZE(M)",

       to_char(t.ACTIVEBLKS*8/(b.x_undosize*1024)*100, '999.99') "Active(%)",

       to_char(t.UNEXPIREDBLKS*8/(b.x_undosize*1024)*100, '999.99') "UnExp(%)",

       to_char(t.EXPIREDBLKS*8/(b.x_undosize*1024)*100, '999.99') "Exp(%)",

       to_char((t.ACTIVEBLKS + t.UNEXPIREDBLKS + t.EXPIREDBLKS)*8/(b.x_undosize*1024)*100, '999.99') "Usage(%)"

from v$undostat t,

     (select sum(bytes)/1024/1024 "X_UNDOSIZE"

from dba_data_files t

where t.tablespace_name = 'UNDOTBS1') b;

결과는 다음과 같은 유형이고요.

BeginTime        EndTime            TXNCOUNT TBS_SIZE(M) Active(%)  UnExp(%)   Exp(%)     Usage(%)

---------------- ---------------- ---------- ----------- ---------- ---------- ---------- ----------

160331 16:37:42  160331 16:47:42       13648        3500    1.14      71.14       6.24      78.52

160331 16:27:42  160331 16:37:42       13347        3500    1.14      72.34       4.90      78.38

160331 16:17:42  160331 16:27:42       13932        3500    1.37      72.32       4.17      77.86

160331 16:07:42  160331 16:17:42       13343        3500    1.17      72.44       3.37      76.98