oracle的undo表空间不足,undo表空间不足,ORA-30036 unable to extend segment by ...

一、一次更新或者删除大量数据，这些数据需要保存在undo表空间中(直到提交或回滚后这些undo表空间中的数据才允许被覆盖)，如果undo表空间不足，就会报ORA-30036错误。

下面是两种解决办法：

1、增加undo表空间大小，或将undo表空间数据文件设置为自动扩展(如果磁盘空间不足也会导致该问题)

2、批量更新或删除数据

二、数据库中存在大量未提交的事物(更新或删除操作)

解决办法：

1、找出这些占用undo回滚段的操作(使用下面语句)

SELECT s.username,

s.sid,

pr.PID,

s.OSUSER,

s.MACHINE,

s.PROGRAM,

rs.segment_id,

r.usn,

rs.segment_name,

r.rssize/1024/1024,

sq.sql_text

FROM v$transaction t, v$session s, v$rollstat r, dba_rollback_segs rs ,v$sql  sq,v$process pr

WHERE s.saddr = t.ses_addr

AND t.xidusn = r.usn

AND rs.segment_id = t.xidusn

AND s.sql_address=sq.address

AND s.sql_hash_value = sq.hash_value

AND s.PADDR=pr.ADDR

ORDER BY t.used_ublk DESC;

USERNAME   SID     PID OSUSER  MACHINE  PROGRAM              SEGMENT_ID  USN  SEGMENT_NAME          R.RSSIZE/1024/1024  SQL_TEXT

--------   ---   --- ------  -------  -------                     ----------  ---  ------------          ------------------  ------------------------

SCOTT       36     25 oracle   rhel5    sqlplus@rhel5 (TNS V1-V3)         14          14   _SYSSMU14_55430887$    5.3671875      update t set object_name='aaaa' where rownum<10000

SCOTT      30     23 oracle   rhel5    sqlplus@rhel5 (TNS V1-V3)         12           12  _SYSSMU12_2606085817$   2.6171875     update b set object_name='aaaa'

找出导致这些语句未提交的原因(如调用存储过程逻辑问题)，进行处理

三、在使用数据泵导入数据时，如果数据中存在大量约束和索引，导入数据时维护索引会产生大量undo数据

Import datapump will perform index maintenance and this can increase undo usage especially if there is other DML occurring on the database。

解决办法：导入数据时使用下面两个参数排除约束和索引，导入数据成功后再创建相应的约束和索引：

EXCLUDE=CONSTRAINT

EXCLUDE=INDEX

备注：

关于undo表空间的大小，可以根据下面的计算做一个预算

1.计算业务高峰期每秒产生undo数据块的个数：

select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;

2.得到undo数据块在undo表空间中可以保留的最长时间

show parameter undo_retention

3.得到数据块大小

show parameter db_block;

4.将以上三者的数据相乘就是所需undo表空间的大小数。

查看undo数据块的历史使用情况(每10分钟使用的数据块数量)

SQL> select begin_time,end_time,undoblks from v$undostat;

16-MAY-16       16-MAY-16             827

16-MAY-16       16-MAY-16            1065

16-MAY-16       16-MAY-16              23

16-MAY-16       16-MAY-16            2279

16-MAY-16       16-MAY-16            8665

16-MAY-16       16-MAY-16            3245

16-MAY-16       16-MAY-16            1011

......

......

......

查看正在运行的事物所需要的undo数据块

SQL> select addr,used_ublk from v$transaction;

ADDR          USED_UBLK

---------------- ----------

000000007DC55940    296

000000007DC5F108    580

查看UNDO表空间占用情况

SQL> select tablespace_name,status,round(sum(bytes)/1024/1024,2) MB,count(*) extent_count from dba_undo_extents group by tablespace_name,status order by tablespace_name,status;

TABLESPACE_NAME            STATUS         MB EXTENT_COUNT

------------------------------ --------- ---------- ------------

UNDOTBS1               ACTIVE          10.69          36

UNDOTBS1               EXPIRED         .5           8

UNDOTBS1               UNEXPIRED      72.81         112

UNDOTBS2               EXPIRED           2.94          32

UNDOTBS2               UNEXPIRED       3.44          10

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30373263/viewspace-2100947/，如需转载，请注明出处，否则将追究法律责任。