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

摘要:
原则上,过期区域不会作为免费PS发布:没有人会在生产中将UNDOTBS的保留设置为“保证”,这非常危险。其他状态的范围可以在空间上重用。USED_UBLK:事务使用的取消锁定数。关于UNDO的其他知识:1。撤消读取模式是单块读取,因此事务回滚很慢。2.显示撤消使用的统计信息:SELECTTO_CHARBEGIN_TIME、TO_CHAREND_TIME、UNDOBLKSFROMV$UNDOSTAT;3.系统表空间中有一个系统回滚段。系统回滚段仅在操作数据字典时使用。在另一种情况下,如果undo表空间有问题,oracle也可以使用系统段。
 

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

show parameter undo_tablespace

UNDO空间满的处理方法(含UNDO的学习与相关解释)第1张

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

set lines 200 pages 200col file_name for a60
col tablespace_name for a20;select tablespace_name,file_name,bytes/1024/1024 MB from dba_data_fileswhere tablespace_name like '%UNDOTBS%';

UNDO空间满的处理方法(含UNDO的学习与相关解释)第2张

3、查看undo表空间属性:

show parameter undo

UNDO空间满的处理方法(含UNDO的学习与相关解释)第3张

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

UNDO空间满的处理方法(含UNDO的学习与相关解释)第4张

解释:

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 a30select tablespace_name,
status,sum(bytes)/1024/1024 MB from dba_undo_extentsgroup by tablespace_name,status;

UNDO空间满的处理方法(含UNDO的学习与相关解释)第5张

与一般的用户表空间不同,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的学习与相关解释)第6张

ADDR: 事务的内存你地址。

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

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

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

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

UNDO空间满的处理方法(含UNDO的学习与相关解释)第7张

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

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

UNDO空间满的处理方法(含UNDO的学习与相关解释)第8张

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,
UNDOBLKSFROM V$UNDOSTAT;

UNDO空间满的处理方法(含UNDO的学习与相关解释)第9张

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的学习与相关解释)第10张

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的学习与相关解释)第11张

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 

作者:Nathon-wang

原文链接:https://www.cnblogs.com/nathon-wang/p/10293919.html

免责声明:文章转载自《UNDO空间满的处理方法(含UNDO的学习与相关解释)》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇MySQL-锁Linux12-内存管理下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

mysql关于“异步复制”“同步复制”“半同步复制”“无损复制”的概念与区别

异步复制:主把事务写到binlog日志并不管从是否接收或者什么时候接收,commit之后,不会与从发生ack之类的交互。 同步复制:当主提交一个事务,在主向前端返回一个commit成功的指令前,必须保证所有的从已经提交了这个事务(所有从不但接收了,还必须apply了该事务日志) 半同步复制:(5.7.2版本之前默认且唯一的一个参数值after_commit...

git 常见命令(补充)

回滚到上个版本 git reset --hard HEAD^ 回滚到上上个版本 git reset --hard HEAD^^ 查看提交记录 git log # 格式化 git log --pretty=oneline 查看变更内容 git diff 查看操作的每一次记录(包括回滚记录,git log在某次回滚后,不会记录这次回滚以后的所有记录,...

MySQL InnoDB表空间加密

MySQL InnoDB表空间加密    从 MySQL5.7.11开始,MySQL对InnoDB支持存储在单独表空间中的表的数据加密 。此功能为物理表空间数据文件提供静态加密。该加密是在引擎内部数据页级别的加密手段,在数据页写入文件系统时加密,加密用的是AES算法,而其解密是在从文件读到内存中时进行。 1 配置加密插件 1.1  修改配置文件 在mys...

SQL SERVER事务处理

事务定义: 事务是单个的工作单元。如果某一事务成功,则在该事务中进行的所有数据更改均会 提交,成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚,则所有 数据更改均被清除。 事务三种运行模式:自动提交事务每条单独的语句都是一个事务。显式事务每个事务均以BEGIN TRANSACTION语句显式开始,以COMMIT或ROLLBACK语句显式结束。隐...

Spring的学习(四、Spring事务管理)

Spring事务管理的三个核心接口 Spring的事务管理是基于AOP实现的,而AOP是以方法为单位的。 Spring的事务属性分别为传播行为、隔离级别、只读和超时属性。所有这些属性提供了事务应用的方法和描述策略。 事务管理的三个核心接口:PlatformTransactionManager、TransactionDefinition、Transactio...

SQL Server 事务、异常和游标

Ø 事务 在数据库中有时候需要把多个步骤的指令当作一个整体来运行,这个整体要么全部成功,要么全部失败,这就需要用到事务。 1、 事务的特点 事务由若干条T-SQL指令组成,并且所有的指令昨晚一个整体提交给数据库系统,执行时,这组指令要么全部执行完成,要么全部取消。因此,事务是一个不可分割的逻辑单元。 事务有4个属性:原子性(Atomicity)、一...