欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

undo空间满的处理方法(含undo的学习与相关解释)

程序员文章站 2022-09-04 18:01:47
1、查看数据库当前实例使用的是哪个UNDO表空间: 2、查看UNDO表空间对应的数据文件和大小 3、查看undo表空间属性: 解释: undo段中区的状态: free: 区未分配给任何一个段 active: 已经被分配给段,并且这个段被事务所使用,且事务没有提交,不能覆盖。 (区被未提交的事务使用) ......

1、查看数据库当前实例使用的是哪个undo表空间:

show parameter undo_tablespace

undo空间满的处理方法(含undo的学习与相关解释)

 

2、查看undo表空间对应的数据文件和大小

set lines 200 pages 200
col file_name for a60
col tablespace_name for a20;
select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files
where tablespace_name like '%undotbs%';

undo空间满的处理方法(含undo的学习与相关解释)

 

3、查看undo表空间属性:

show parameter undo

undo空间满的处理方法(含undo的学习与相关解释)

select retention,tablespace_name from dba_tablespaces where tablespace_name like '%undotbs%';

undo空间满的处理方法(含undo的学习与相关解释)

解释:

undo段中区的状态:
free:     区未分配给任何一个段
active:  已经被分配给段,并且这个段被事务所使用,且事务没有提交,不能覆盖。 (区被未提交的事务使用)       
unexpired:事务已经提交,但是区还在段中,还没有被覆盖且未达到undo_retention设定的时间。
    (nogurantee的情况下,原则上oracle尽量的不覆盖unexpired的区,但是如果undo空间压力及较大,oracle也会去覆盖。如果是guarantee,oracle强制保留retention时间内的内容,这时候free和expired空间不足的话,新事物将失败。)
expired:oracle希望已经提交的事务对应的undo表空间中的undo段中的区再保留一段时间。保留的时间就是undo_retention。
     unexpired的区存在时间超过undo_retention设定的时间,状态就会变为expired。过期后的区就可以被覆盖了。原则上expired的区一般不会释放成free
ps:生产中没有人会将undotbs的retention设置成guarantee这是很危险的。

 

4、查看undo表空间当前的使用情况:

set lines 200 pages 200  
col tablespace_name for a30
select tablespace_name,status,sum(bytes)/1024/1024 mb from dba_undo_extents
group by tablespace_name,status;

undo空间满的处理方法(含undo的学习与相关解释)

与一般的用户表空间不同,undo表空间不能通过dba_free_spaces来确定实际的使用情况,undo表空间除了active状态的extent不能被覆盖外。其他状态的extent都是可以空间复用的。

如果active的extent总大小很大,说明系统中存在大事务。如果undo资源耗尽(active接近undotbs的总大小),可能导致事务失败。

 

5、查看什么事务占用了过多的undo:

select addr,used_ublk,used_urec,inst_id from gv$transaction order by 2 desc;

undo空间满的处理方法(含undo的学习与相关解释)

addr: 事务的内存你地址。

used_ublk:事务使用的undo block数量。

used_urec:事务使用的undo record (undo前镜像的条数,例如:delete删除的记录数)

 

6、查看占用undo的事务执行了什么sql:

set lines 200 pages 200
col program for a30
col machine for a30
select sql_id,last_call_et,program,machine from gv$session where taddr='0000000089a9e2f0';

undo空间满的处理方法(含undo的学习与相关解释)

last_call_et: 上一次调用到现在为止过了多长时间,单位为秒,途中显示过了304s (既可以理解为sql已经运行了304s)。

 

set long 99999
set lines 100 
set pages 1000 
select sql_fulltext from v$sql where sql_id='8gvp49tr474f2';

undo空间满的处理方法(含undo的学习与相关解释)

 

7、找到了sql,下面就可以联系应用做处理了:

哪台机器,通过什么程序,发起了什么sql,占用了多少undo,是否可以杀掉,sql是否可以改写,是否可以分批提交。。。等

 

 

 关于undo的其他知识:

1、undo的读取方式是单块读的,所以事务的回滚比较慢

2、显示undo使用情况的统计信息:

select 
to_char(begin_time,'hh24:mi:ss') begin_time,
to_char(end_time,'hh24:mi:ss') end_time,
undoblks
from v$undostat;

undo空间满的处理方法(含undo的学习与相关解释)

3、system表空间中有一个系统回滚段,只有在对数据字典进行操作时(eg:修改表结构)才用到系统回滚段,另外一种情况,如果undo表空间出现问题,oracle也可能使用system段。

一个事务开始的时候,在shared pool中分一个imu(in memory undo) buffer,将所有的回滚信息写到imu buffer中
一个事务开始后,需要回滚块的时候不需要从从磁盘读undo block,直接从shared pool 中分imu buffer,之后回滚信息写到imubuffer中,
回滚信息写入的时候也要产生redo,但是imubuffer减少了物理io
针对imubuffer 在shared中会生成专门供其使用的redo日志区,叫做private redo
undo空间满的处理方法(含undo的学习与相关解释)

 

4、undo segment的信息:

select 
a.name, b.xacts, b.writes, b.extents
from 
v$rollname a, v$rollstat b
where a.usn=b.usn;

undo空间满的处理方法(含undo的学习与相关解释)

usn          rollback segment number
xacts         number of active transactions
extents     number of extents in the rollback segment
writes      number of bytes written to the rollback segment