今天在为一套库扩表空间时查看到UNDOTBS1/2使用率均超过了90%,监控居然没报警,呵呵,这种花钱买监控还不如Nagios

SQL> select * from v$version;   

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> set linesize 200 SQL> col file_name for a40 SQL> select 2 a.a1 tabelspace_name, 3 round(b.b3/1048576,0) table_size_M, 4 round((b.b3-a.a2)/1048576,0) used_M, 5 round(a.a2/1048576,0) free_M, 6 round(substr((b.b3-a.a2)/b.b3*100,1,5),2) used_rate 7 from 8 (select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a, 9 (select tablespace_name b1,sum(bytes) b3 from dba_data_files group by tablespace_name) b 10 where a.a1(+)=b.b1 11 order by 5 desc; TABELSPACE_NAME TABLE_SIZE_M USED_M FREE_M USED_RATE ------------------------------ ------------ ---------- ---------- ---------- UNDOTBS1 5000 1009 3991 97.33 UNDOTBS2 5000 835 4165 91.59 SYSAUX 5000 3738 1262 74.75 MNTDATA 153600 110900 42700 72.2 SYSTEM 5000 286 4714 5.72 USERS 500 2 498 .36 MNTINDEX 30720 1 30719 0

查看unexpired和expired的大小

SQL> select tablespace_name,count(bytes/1024/1024) from dba_undo_extents where status!='ACTIVE' group by tablespace_name; TABLESPACE_NAME count(bytes/1024/1024) ------------------------------ ---------------- UNDOTBS1 4866 UNDOTBS2 4579

actived一个没有,全是expired和unexpired的

SQL> select tablespace_name,count(bytes/1024/1024) from dba_undo_extents where status='ACTIVE' group by tablespace_name; no rows selected

SQL>show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1

undo retention为900
UNDO表空间并没有设置成GUARANTEE模式，所以根据我们的知识都明白UNDO表空间中的EXPIRED和UNEXPIRED都是可能被重用的

SQL> select undotsn,activeblks,expiredblks,unexpiredblks,tuned_undoretention from v$undostat; UNDOTSN ACTIVEBLKS EXPIREDBLKS UNEXPIREDBLKS TUNED_UNDORETENTION ---------- ---------- ----------- ------------- ------------------- 4 464 84016 22272 2361 4 464 84016 22272 2300 4 464 83888 23680 1985 4 464 85936 20992 2296 4 464 82992 26240 1694 4 464 280840 13056 2579 4 464 281352 11648 2287 4 464 123384 462216 2574 4 464 125040 460560 176361 4 464 125144 460456 176482 4 464 125120 460480 176583 UNDOTSN ACTIVEBLKS EXPIREDBLKS UNEXPIREDBLKS TUNED_UNDORETENTION ---------- ---------- ----------- ------------- ------------------- 4 464 70864 456752 226010 4 464 68688 456880 225899 4 464 69072 456496 225960 4 464 66768 458800 225860 4 464 66768 458800 225608 4 464 66896 458672 225628 4 464 65744 459824 226171 4 464 67920 457648 225325 4 464 64464 461104 225395 4 464 64720 460848 225330 4 464 64720 460848 225049 UNDOTSN ACTIVEBLKS EXPIREDBLKS UNEXPIREDBLKS TUNED_UNDORETENTION ---------- ---------- ----------- ------------- ------------------- 4 464 66768 459824 225003 4 464 71888 459952 225888 4 464 71888 458928 225985 4 464 71120 459696 226087 4 464 70096 460720 225874 4 464 70992 459824 225904 4 464 69072 461744 225913 4 464 69072 460720 225672 4 464 67920 461872 225690 4 464 65872 463920 225357 4 464 65872 463920 225166 … 576 rows selected.

TUNED_UNDORETENTION被自动调整了