Oracle undo表空间不释放

Oracle undo表空间不释放

𝓓𝓸𝓷 Lv6
  2024-07-16 12:17:36 2024-07-16 12:17:36 Created   2025-09-08 11:42:35 2025-09-08 11:42:35 Updated    

Oracle undo表空间增长不释放解决方法，从10.2版本开始，oracle默认开启隐含参数_undo_autotune，使undo进行自动调节。 但会引发bug 9681444 在12.1版本修复。对于undo datafile是非自动扩张的情况下，oracle为了避免ora01555错误，会进行_undo_retention自动调节。在Oracle进行undo_retention自动调节的情况下，手动设置参数undo_retention将通常不会起作用

  • undo datafile autoextend off

结合undo表空间大小，根据v$undostat.TUNED_UNDORETENTION来决定undo_retention的大小，这种情况往往TUNED_UNDORETENTION值会很大，但undo空间使用成为数据库运行瓶颈

  • undo datafile autoextend on

根据v$undostat.MAXQUERYLEN+300来决定undo_retention的大小,最后取max(MAXQUERYLEN+300,undo_retention)最大值

一、查看undo表空间使用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
SQL> set linesize 300 pagesize 300
col tablespace_name for a16
set linesize 300 pagesize 300
with free_sz as
(select tablespace_name, round(sum(f.bytes) /1024/1024/1024,2) free_gb
    from dba_free_space f
   group by tablespace_name),
a as
(select tablespace_name,
         round(sum(case
               when status = 'ACTIVE' then
                blocks
             end) * 8 /1024/1024,2) active_gb,  
         round(sum(case
               when status = 'EXPIRED' then
                blocks
             end) * 8 /1024/1024,2) expired_gb,        
         round(sum(case
               when status in ('ACTIVE', 'UNEXPIRED') then
                blocks
             end) * 8 /1024/1024,2) active_unexpired_gb       
    from dba_undo_extents
   where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED')
   group by tablespace_name),
undo_sz as
(select tablespace_name, round(sum(df.user_bytes) /1024/1024/1024,2) user_sz_gb
    from dba_tablespaces ts
    join dba_data_files df
   using (tablespace_name)
   where ts.contents = 'UNDO'
     and ts.status = 'ONLINE'
     group by tablespace_name)
select tablespace_name,
       user_sz_gb,
       free_gb,
       active_gb,
       expired_gb,
       active_unexpired_gb,
       free_gb + expired_gb + active_unexpired_gb total_gb
  from undo_sz
  join free_sz using (tablespace_name)
  join a using (tablespace_name);
  
TABLESPACE_NAME  USER_SZ_GB    FREE_GB	ACTIVE_GB EXPIRED_GB ACTIVE_UNEXPIRED_GB   TOTAL_GB
---------------- ---------- ---------- ---------- ---------- ------------------- ----------
UNDOEMR 		 30	  1.41			1.28		   27.31	 30
UNDOEMR2		 30	 16.51		       12.84		     .65	 30

set num 30
SELECT DISTINCT STATUS,SUM(BYTES)/1024/1024,COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

二、查看undo状态

通过查询v$rollstat中的shrink，warp字段（从一个回滚段切换到另一个回滚段次数） 如果这两个参数值特别大，查询是哪个异常事务在使用回滚段。 warp是事务大，跨回滚段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SQL> select d.segment_name,
       d.tablespace_name,
       s.waits,
       s.shrinks,
       s.wraps,
       s.status
  from v$rollstat s, dba_rollback_segs d
 where s.usn = d.segment_id
 order by 1;
 
 SEGMENT_NAME		       TABLESPACE_NAME		 WAITS	  SHRINKS      WRAPS STATUS
------------------------------ -------------------- ---------- ---------- ---------- --------
SYSTEM			       SYSTEM			     0		0	   0 ONLINE
_SYSSMU21_811310075$	       UNDOEMR			   241		0	2214 ONLINE
_SYSSMU22_3584902470$	       UNDOEMR			   275		0	2144 ONLINE
_SYSSMU23_3365113092$	       UNDOEMR			   279		0	2178 ONLINE
_SYSSMU24_1488431328$	       UNDOEMR			   282		0	2150 ONLINE
_SYSSMU25_2029519986$	       UNDOEMR			   331		0	1891 ONLINE
_SYSSMU26_382053670$	       UNDOEMR			   258		0	2155 ONLINE
_SYSSMU27_525317525$	       UNDOEMR			   279		0	2153 ONLINE
_SYSSMU28_1438931338$	       UNDOEMR			   919		0	2129 ONLINE
_SYSSMU29_4007536970$	       UNDOEMR			 19222	      134	1493 ONLINE
_SYSSMU30_95470165$	           UNDOEMR			368420	      524	7652 ONLINE

11 rows selected.


select INST_ID, to_char(BEGIN_TIME,'YYYY/MM/DD HH24:MI:SS') "BEGIN TIME", END_TIME, UNDOBLKS, TXNCOUNT, UNXPBLKRELCNT, ACTIVEBLKS, UNEXPIREDBLKS, EXPIREDBLKS 
 from gv$undostat order by 2;
 
 select usn,
       xacts,
       status,
       rssize / 1024 / 1024,
       hwmsize / 1024 / 1024,
       shrinks
from v$rollstat
order by rssize;

三、查看undo参数

_undo_autotune 为 true 的状况下，UNDO 的上限被自动管理，并且不受 undo_retention 的控制，而是上限受到 _highthreshold_undoretention(单位为秒)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> show parameter undo_

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled		     boolean	 FALSE
undo_management 		     string	 AUTO
undo_retention			     integer	 900
undo_tablespace 		     string	 UNDOEMR


SQL> set linesize 300 pagesize 300
col Max_maxquerylen for a30
col Min_tuned_undoretention for a30
col Max_tuned_undoretention for a30
 			
select max(maxquerylen)||' Second' as "Max_maxquerylen", 
       round(min(tuned_undoretention)/60/60/24)||' Days'  as "in_tuned_undoretention",
	   round(max(tuned_undoretention)/60/60/24)||' Days' as "Max_tuned_undoretention"
from v$undostat; 

Max_maxquerylen 	       Min_tuned_undoretention	      Max_tuned_undoretention
------------------------------ ------------------------------ ------------------------------
1280 Second		                         30 Days			           36 Days

四、查看数据文件是否自动扩展

1
2
3
4
5
6
7
8
9
10
11
12
SQL> col FILE_NAME format a50  
col TABLESPACE_NAME format a20 
set num 50
select file_id,file_name,tablespace_name,autoextensible,maxbytes from dba_data_files where tablespace_name in ('UNDOTBS1', 'UNDOTBS2','UNDOEMR') order by file_id; 

   FILE_ID FILE_NAME					      TABLESPACE_NAME	   AUT
---------- -------------------------------------------------- -------------------- ---
	33 +DATA/TPCDB1/E64AC40F3F99772CE055000000000001/DATA UNDOEMR		   NO
	   FILE/undoemr.535.1136750767

	46 +DATA/TPCDB1/E64AC40F3F99772CE055000000000001/DATA UNDOTBS2		   YES
	   FILE/undotbs2.383.1136646535

五、查看哪些用户占用

1
2
3
4
select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from v$session a,
v$transaction b
where a.saddr = b.ses_addr;

六、查看undo表空间哪些SQL占用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select *
  from (select maxqueryid, round(sum(undoblks) * 8 / 1024) consumed_size_MB
          from v$undostat
         group by maxqueryid
         order by consumed_size_MB desc)
 where rownum < 50;
 
 MAXQUERYID    CONSUMED_SIZE_MB
------------- ----------------
0rc4km05kgzb9		374528
0cd1z0ss788nq		 33342
00myywmd5qpy0		  5475
5kkm0p9hh3dzg		  2176
b7udzu0wpu0r8		  1846
0jmmxyha33h9k		   208
dhhd6rs34az40		    27
fc05q7sm5c31j		     3
			                  0
9 rows selected.

七、Kill正在占用undo表空间的会话(可选)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  'alter system kill session ''' || s.sid || ',' || s.serial# || '''' || ';'
from
  v$session s,
  v$transaction t,
  v$rollname r,
  v$rollstat g,
  dba_rollback_segs h,
  v$sqlarea i
where
  t.addr = s.taddr
  and t.xidusn = r.usn
  and r.usn = g.usn
  and r.name = h.segment_name
  and s.PREV_SQL_ID = i.SQL_ID
  and h.tablespace_name = 'UNDOTBS'
  and s.status = 'INACTIVE';

八、解决方法

设置undo表空间数据文件自动扩展，并设置最大值:

1
2
3
SQL> alter database datafile '+DATA/TPCDB1/E64AC40F3F99772CE055000000000001/DATAFILE/undoemr.535.1136750767' autoextend on maxsize 30g;

等待: Max_maxquerylen+300，即1280+300=1580秒

关闭隐藏参数:

1
alter system set "_undo_autotune"=false scope=both sid='oradb1';

开启debug参数:

_smu_debug_mode的含义，请见mos:420525.1

1
alter system set "_smu_debug_mode"=33554432 scope=both sid='oradb1';

如果以上方法不行，可以手动使用下面方法:

1
ALTER SYSTEM SET "_HIGHTHRESHOLD_UNDORETENTION"=<new_value>;
  • Title: Oracle undo表空间不释放
  • Author: 𝓓𝓸𝓷
  • Created at : 2024-07-16 12:17:36
  • Updated at : 2025-09-08 11:42:35
  • Link: https://www.zhangdong.me/oracle-undo-tablespace.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
处理Oracle undo表空间不释放问题 处理Oracle undo表空间不释放问题 查看Oracle表空间使用情况 查看Oracle表空间使用情况 查看Oracle表空间增长过快原因 查看Oracle表空间增长过快原因
推荐文章
处理Oracle undo表空间不释放问题 处理Oracle undo表空间不释放问题 查看Oracle表空间使用情况 查看Oracle表空间使用情况
评论
  1. 一、查看undo表空间使用情况
  2. 二、查看undo状态
  3. 三、查看undo参数
  4. 四、查看数据文件是否自动扩展
  5. 五、查看哪些用户占用
  6. 六、查看undo表空间哪些SQL占用
  7. 七、Kill正在占用undo表空间的会话(可选)
  8. 八、解决方法