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
;