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 ;