检测undo使用情况
检查fre space
select sum(bytes)/1024/1024 MB from dba_free_space where tablespace_name='UNDOTBS1';
select sum(bytes)/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';
检查是否undo datafile 是否自动增长
select autoextensible from dba_data_files where tablespace_name=’ 'UNDOTBS1'’
检查Undo extents使用状态
SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 MB, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*),TABLESPACE_NAME FROM DBA_UNDO_EXTENTS GROUP BY STATUS,TABLESPACE_NAME;
SQL> select sum(bytes)/1024/1024 MB from dba_free_space where tablespace_name='UNDOTBS1';
MB
----------
22128.625
SQL> select sum(bytes)/1024/1024 MB from dba_data_files where tablespace_name='UNDOTBS1';
MB
----------
22370
SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 MB, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
STATUS MB COUNT(*)
--------- ---------- ----------
UNEXPIRED 4.4375 41
EXPIRED 235.875 884
ACTIVE 1 1
SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*),TABLESPACE_NAME FROM DBA_UNDO_EXTENTS GROUP BY STATUS,TABLESPACE_NAME;
STATUS SUM(BYTES) COUNT(*) TABLESPACE_NAME
--------- ---------- ---------- ------------------------------
EXPIRED 247332864 884 UNDOTBS1
UNEXPIRED 4653056 41 UNDOTBS1
ACTIVE 1048576 1 UNDOTBS1
情况一
没有Expired extents大量Active extents
解决方法:
增加undo tablespace
情况二
没有Expired extents 大量Unexpired extents
表明undo space 不能满足undo_retention要求
解决方法:
增大undo tablespace 或者减小undo_retention
情况三
有大量的Expired extents
由于bug引起 已知bug
Bug 5442919 - Expired extents not being reused (ORA-30036) (Doc ID 5442919.8)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8494287/viewspace-1691901/ ，如需转载，请注明出处，否则将追究法律责任。
