undostat 확인 SQL문

1. 소개

v$undostat을 확인하는 SQL문이다.

---

2. SQL

set linesize 100
col BeginTime for a16
col EndTime for a16

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
;