ora-30036诊断

检测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博客 ” ，链接：http://blog.itpub.net/15747463/viewspace-1061168/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15747463/viewspace-1061168/