We had this issue where undo space was not released even when having a low undo_retention (900).
This was because TUNED_UNDORETENTION was very high:
SQL> SELECT tablespace_name, sum(blocks), status FROM dba_undo_extents GROUP BY tablespace_name, status order by 1;
TABLESPACE_NAME SUM(BLOCKS) STATUS
------------------------------ ----------- ----------
UNDOTBS1 1408 ACTIVE
UNDOTBS1 272 EXPIRED
UNDOTBS1 999728 UNEXPIRED
UNDOTBS2 4060416 UNEXPIRED <<-----
SQL>
SQL> select maxquerylen,to_char(begin_time,'DD-MON-YYYY HH24:MI:SS'), tuned_undoretention from v$undostat order by begin_time asc;
MAXQUERYLEN TO_CHAR(BEGIN_TIME,'DD-MON-YY TUNED_UNDORETENTION
----------- ----------------------------- -------------------
677 20-JUN-2022 17:20:18 993193
1286 20-JUN-2022 17:30:18 993856
692 20-JUN-2022 17:40:18 994457
1300 20-JUN-2022 17:50:18 995059
1049 20-JUN-2022 18:00:18 995661
1655 20-JUN-2022 18:10:18 996263
1923 20-JUN-2022 18:20:18 996864
0 20-JUN-2022 18:30:18 997405
735 20-JUN-2022 18:40:18 998006
1344 20-JUN-2022 18:50:18 998607
1352 20-JUN-2022 19:00:18 999268
1360 20-JUN-2022 19:10:18 999871
1369 20-JUN-2022 19:20:18 1000475
1375 20-JUN-2022 19:30:18 1001077
1383 20-JUN-2022 19:40:18 1001681
187 20-JUN-2022 19:50:18 1002223
796 20-JUN-2022 20:00:18 1002824
1402 20-JUN-2022 20:10:18 1003425
209 20-JUN-2022 20:20:18 1004025
816 20-JUN-2022 20:30:18 1004628
226 20-JUN-2022 20:40:18 1005232
834 20-JUN-2022 20:50:18 1005833 On this case, we followed “IF: Causes for High Undo Tablespace Space Usage (Doc ID 1951402.1)” and matched size with maxsize for the undo datafiles:
SQL> select file_name, autoextensible, bytes/1024/1024 as "MB" , maxbytes/1024/1024 as "Max MB" from dba_data_files where tablespace_name like '%UNDO%';
FILE_NAME AUT MB Max MB
---------------------------------------------------- --- ---------- ----------
+DATA001/PRODIS/DATAFILE/undotbs2.285.1073656051 YES 14700 32767.9844
+DATA001/PRODIS/DATAFILE/undotbs1.279.1073655553 YES 24340 32767.9844
ALTER DATABASE DATAFILE '+DATA001/PRODIS/DATAFILE/undotbs2.285.1073656051' AUTOEXTEND ON MAXSIZE 32767M;
ALTER DATABASE DATAFILE '+DATA001/PRODIS/DATAFILE/undotbs2.285.1073656051' RESIZE 32767M;
ALTER DATABASE DATAFILE '+DATA001/PRODIS/DATAFILE/undotbs1.279.1073655553' AUTOEXTEND ON MAXSIZE 32767M;
ALTER DATABASE DATAFILE '+DATA001/PRODIS/DATAFILE/undotbs1.279.1073655553' RESIZE 32767M;
SQL> select file_name, autoextensible, bytes/1024/1024 as "MB" , maxbytes/1024/1024 as "Max MB" from dba_data_files where tablespace_name like '%UNDO%';
FILE_NAME AUT MB Max MB
---------------------------------------------------- --- ---------- ----------
+DATA001/PRODIS/DATAFILE/undotbs2.285.1073656051 YES 32767 32767
+DATA001/PRODIS/DATAFILE/undotbs1.279.1073655553 YES 32767 32767After a few hours (changes are not reflected right away), we can see TUNED_UNDORETENTION has a better value and extents are being released with no issue:
SQL> select maxquerylen,to_char(begin_time,'DD-MON-YYYY HH24:MI:SS'), tuned_undoretention from v$undostat order by begin_time asc;
MAXQUERYLEN TO_CHAR(BEGIN_TIME,'DD-MON-YY TUNED_UNDORETENTION
----------- ----------------------------- -------------------
1532 21-JUN-2022 00:40:18 963985
338 21-JUN-2022 00:50:18 964050
943 21-JUN-2022 01:00:18 963468
1548 21-JUN-2022 01:10:18 962852
1249 21-JUN-2022 01:20:18 962975
1633 21-JUN-2022 01:30:18 964402
2239 21-JUN-2022 01:40:18 963887
1868 21-JUN-2022 01:50:18 963941
1273 21-JUN-2022 02:00:18 963806
1608 21-JUN-2022 02:10:18 963172
1293 21-JUN-2022 02:30:18 2197 <<----- New values
1301 21-JUN-2022 02:40:18 2143
707 21-JUN-2022 02:50:18 1734
1315 21-JUN-2022 03:00:18 2341
1922 21-JUN-2022 03:10:18 2766
1331 21-JUN-2022 03:20:18 2174
1337 21-JUN-2022 03:30:18 2180
1343 21-JUN-2022 03:40:18 2186
1180 21-JUN-2022 03:50:18 2022
1787 21-JUN-2022 04:00:18 2571
1365 21-JUN-2022 04:10:18 2148
SQL> SELECT tablespace_name, sum(blocks), status FROM dba_undo_extents GROUP BY tablespace_name, status order by 1;
TABLESPACE_NAME SUM(BLOCKS) STATUS
------------------------------ ----------- ---------
UNDOTBS1 128 ACTIVE
UNDOTBS1 6768 EXPIRED
UNDOTBS1 17104 UNEXPIRED
UNDOTBS2 8288 EXPIRED
UNDOTBS2 67264 UNEXPIRED <<-- vs 4060416 from earlier
SQL>
Comments