查看是否开启retention guarantee
select tablespace_name,retention from dba_tablespaces where contents='UNDO'
查看指定undo表空间三种状态(active/unexpired/expired)的使用情况
select tablespace_name, status, round( sum( bytes ) / 1048576, 2 ) mb,
count(*) extent_count
from dba_undo_extents
where tablespace_name='UNDOTBS2'
group by tablespace_name, status
order by tablespace_name, status;
undo表空间三种状态(active/unexpired/expired)解释:
实际undo使用率
select ((select (nvl(sum(bytes),0))
from dba_undo_extents
where tablespace_name = '&TABLESPACE_NAME'
and status in ('ACTIVE','UNEXPIRED')) *100) /
(select sum(bytes)
from dba_data_files
where tablespace_name = '&TABLESPACE_NAME') "PCT_INUSE"
from dual;
查看当前占用undo表空间的事务
SELECT S.USERNAME, S.SID,
S.SERIAL#,
S.LOGON_TIME,
s.SQL_ID,
T.XIDUSN,
T.UBAFIL,
T.UBABLK,
T.USED_UBLK,
T.START_DATE,
T.STATUS
FROM V$SESSION S, V$TRANSACTION T
WHERE S.SADDR = T.SES_ADDR
ORDER BY t.USED_UBLK DESC;
--USERNAME:会话的用户名
--SID:会话的标识符
--SERIAL#:会话的序列号
--LOGON_TIME:会话的登录时间
--SQL_ID:会话正在执行的 SQL 语句的标识符
--XIDUSN:事务的 Undo 段号
--UBAFIL:Undo 块的文件号
--UBABLK:Undo 块的块号
--USED_UBLK:使用的 Undo 块数
--START_DATE:事务的开始时间
--STATUS:事务的状态
参考:
老生常谈:关于undo表空间的使用率 - AlfredZhao - 博客园
Oracle undo 表空间使用情况分析 - Maxwell_Yang - 博客园