A reader, June 24, 2020 - 1:50 am UTC
Thanks, Connor. I'll take a look.
BTW: three hours later, NOW the used space has been freed up .... sigh, I hate not being able to control that.
@fs2
FILE_NAME ALLOCATED_MB USED_MB FREE_SPACE_MB
---------------------------------------------------------------------- ------------ ---------- -------------
/u202/oradata/ora/undotbs01.dbf 19456 83 19372
/u203/oradata/ora/undotbs03.dbf 12824 78 12745
/u203/oradata/ora/undotbs02.dbf 32740 5 32734
------------ ---------- -------------
sum 65020 166 64851
select status, sum(bytes)/1024/1024 MB from DBA_UNDO_EXTENTS group by status order by status;
STATUS MB
--------- ----------
EXPIRED 76.6875
UNEXPIRED 87.875
June 26, 2020 - 4:14 am UTC
You can have additional level of control via underscore parameters, but you'd need to liaise with Support to get their guidance on that one
viewed the youtube video and have a follow-up question
John Cantu, June 24, 2020 - 2:09 am UTC
If the Oracle database can/will ignore the UNDO_RETENTION value, how can DBA ever know if the UNDO tablespace will run out of space? Let me elaborate. Say I have three UNDO datafiles sized 20 G each, and each is set to auto allocate up to a max size of 30G. A user runs a purge job that removes 100 million records with commits executed every 100k rows removed. Then the DBA see each of the datafiles keep increasing in size until they are all sized close to 30G. Are we at risk of that purge job failing at some point because Oracle decided on it's own to keep COMMITTED undo data? Or will Oracle allow the purge transaction to succeed by releasing undo space by removing committed data from the undo?
It is nerve wrecking to see the UNDO tablespace continue to grow to the limit and upsetting to find out that Oracle has gone rogue by ignoring the DBA's request how much old committed data should be saved in the DBA's UNDO_RETENTION setting.
June 26, 2020 - 4:24 am UTC
Well...its really just a mindset no?
I mean, after all, you've been happily running with a TEMP tablespace that typically reports 100% full since Oracle 7.3.
If you want to *cap* your undo size, you can make the undo datafiles non-extendable. We will choose to purge unneeded data before returning a "no more undo available".
A reader, June 26, 2020 - 2:50 pm UTC
Great point, Conner!
It would still be a nice feature if Oracle would at least change the undo extents to EXPIRED once the DBA set UNDO_RETENTION was reached.
In one case, even AFTER 2 HOURS after a huge purge job completed, "used_mb" showed that the database was still holding on to those UNDO EXTENTS used for the purge job.
This is the query I used:
SELECT SUBSTR (df.file_name, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,
trunc((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
trunc(NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) free_space_mb
FROM dba_data_files df, dba_free_space dfs
WHERE df.FILE_ID = dfs.file_id(+)
GROUP BY dfs.file_id, df.file_NAME, df.file_id, df.bytes
UNION ALL
select file_name, bytes/1024/1024 "allocated_mb",user_bytes/1024/1024 "used_mb", ((bytes/1024/1024) - (user_bytes/1024/1024)) "free_space_mb"
from dba_temp_files
order by used_mb
;
Any thoughts on this?
June 29, 2020 - 5:51 am UTC
It would still be a nice feature if Oracle would at least change the undo extents to EXPIRED
By the same token, I'm sure we'd then have people on AskTOM then telling us: "Why is background process XXX using CPU/IO when it doesn't need to" :-)
In terms of 2 hours, it could hold them "forever" for all I care (in the sense that...if no-one else needs that space right now, then why go to the effort of cleaning it up).
That is sort of a general "trend" in the database. "Delay if you can, what you can do today until tomorrow, because hopefully tomorrow never comes".
eg every time we change a datafile block, we don't write it to disk, because hopefully another change will come along and we'll just keep changing in the buffer cache until we absolutely *must* flush it to disk.