检测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

情况一

ITPub博客

ora-30036诊断

没有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博客

ora-30036诊断

没有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)

最新文章

由于bug引起已知bug