Uh … this was a long journey. In September 2022 Jannes Arends contacted me showing me his growing SYSTEM tablespace with every patch cycle. And not only for the CDB$ROOT only but in every PDB. Over time, I wrote some blog posts I link at the end of this blog post. But finally, we should have a working solution without any additional risk. So, I am hoping that this will be my final blog post about datapatch cleanup and purge.
Photo by Clay Banks on Unsplash
What’s the point (summary)?
With every patch run, datapatch stores the entire collection of rollback scripts (sql and plsql) in a zip file in the SYSTEM tablespaces. The previous zips aren’t needed anymore since the newest always contains the previous ones. But since this zip grows unavoidably with every patch run, more and more space is wasted. And this waste-curve grows.
Here you can find queries and more.
Santosh and the datapatch team then developed a solution. It took a bit since we also needed to ensure to release the space. All looked fine – until the regression testing team gave a red alert. In one of the tests there was an issue with some check queries in the mechanism. They had to be taken out – and the patch got pulled from the RU content.
The same did happen with the next attempt since it turned out that the purge of this metadata which was supposed to happen automatically took over 7 hours on a heavy regression testing environment which had gotten a lot of patches over time.
A decision then was taken by Santosh and the datapatch team to add a flag instead of making the purge the default. This way, you can decide when you want to run it.
Finally, relief
Lots of back and forth. And finally, this is the patch number you need:
One-off patches are available for 19.25.0, 19.26.0 and 23.7.0 currently.
It adds a new flag:
- ./datapatch -purge_old_metadata
This will trigger the cleanup. And you can decide when you want to do it. The fix is supposed to be included in the July 2025 Release Updates (19.28.0).
Did I write FINAL?
Added July 14, 2025
Thanks to Andreas Groetz, I learned today:
There is a new fix available:
- Bug 37738908 – SEPARATE PURGE_OLD_METADATA FROM PATCHING ACTIVITY IN DATAPATCH
What does the fix for Bug 37738908 do differently than the previous one for Bug 37352486?
It simply decouples the purge/cleanup from patching. This means, when you call:
./datapatch -purge_old_metadata
with the previous fix, bug 37352486, it will cleanup AND patch.
But when you applied the fix for bug 37738908, it will ONLY cleanup with the above call, and WON’T patch.
As usual, thanks Andreas!
Did I REALLY write FINAL?
Added July 31, 2025
The fix was supposed to be included in 19.28, and it still isn’t.
Why is that? There was one final change which had to be done:
Decouple the cleanup from the patch run. So, you either “patch” or you “cleanup” but not both at the same time.
This decoupling lead to missing the cutoff time for 19.28. The fix will be actually in 19.29. Sorry for all the inconvenience.
More to add …
Added June 8, 2026
A customer pointed me to this MOS note:
(KB153775) Can Folders/Files under $ORACLE_HOME/sqlpatch/<DB RU Patch Number> be deleted to regain free space? which explains when and how you can manually cleanup.
And even more …
Added June 10, 2026
See KB813695: Applying 19.29 DB RU which has fix of Bug 37738908 does not reduce the REGISTRY$SQLPATCH_RU_INFO Size about the decoupling of purge and patch apply which was done in regards to not harm performance.
Further Links and Information
- Final blog post about datapatch cleanup and purge
- More about cleaning up datapatch patching files
- Blog Post: Cleanup Datapatch Patching Files In Your Database
- Blog Post: More about cleaning up datapatch patching files
- Datapatch User Guide (Doc ID 2680521.1)
- Script: check_patches_19.sql
- Script: unload_blob_from_table.sql
- Script: check_patches_size.sql
- Vegard Kåsa’s Blog Post: Datapatch seems to hang
- (KB153775) Can Folders/Files under $ORACLE_HOME/sqlpatch/<DB RU Patch Number> be deleted to regain free space?
- (KB813695) Applying 19.29 DB RU which has fix of Bug 37738908 does not reduce the REGISTRY$SQLPATCH_RU_INFO Size
–Mike
Hi Mike. Thanks. Works great. README states: “Ensure that you shut down all the services running from the Oracle home.” But I do not think this is true….
Hi Peter,
I agree – and I checked with the team meanwhile. To me, this fix should be applicable even online since it updates only sqlpatch.pl and sqlpatch.pm which get used only when you call datapatch.
Let’s see what we’ll get.
Thanks,
Mike
Hi Mike, did you receive a reply from the team regarding the online installation of the patch?
cheers
Sebastian
Yes, I did.
Oh … glad you asked.
So, yes, it is technically possible and won’t cause any issues. But the patch needs to be packaged and labeled differently which isn’t just an XML flag as I hoped it would be. Eventually, they will try to adopt this. And the team has well understood how to do it. But I can’t give any commitment that they’ll repackage or adjust this at the moment.
Thanks,
Mike
Hi Mike,
Will this flag be included in auto upgrade?
Thank you.
Hi Ian,
no, we can’t. But I can raise this with the team whether they are willing to add it as a separate option.
Automatically is not wanted since it may take a long time when run for the first time, especially in a CDB environment with many PDBs.
Cheers,
Mike
Hi Mike,
Is this relevant to OoP patching and if so would the flag be added to Autoupgrade?
Thank you.
Hi Ian,
yes, it is since you will carry around the removal scripts still in your database. Check the query from this blog post:
https://mikedietrichde.com/2024/01/17/datapatch-stores-rollback-patching-files-in-the-database/
and run it in your DB to see how much space it accumulated.
Cheers,
Mike
./datapatch -version
SQL Patching tool version 19.26
Build label: RDBMS_19.26.0.0.0DBRU_AIX.PPC64_250117
./datapatch -help [shows in part]
-ddl_lock_timeout
Set DDL_LOCK_TIMEOUT to the value specified
but get
./datapatch -ddl_lock_timeout 30
Unknown option: ddl_lock_timeout
That is interesting since we have this in the slides as well.
I mailed the datapatch team since I can verify the same finding you’ve had easily, too.
Cheers,
Mike
Got a reply already:
Filed under bug 37078004
Cheers,
Mike
I like that things are going to be cleaned up. a) opatch util deleteinactivepatches b) datapatch -purge_old_metadata – but what is about c) $ORACLE_HOME/sqlpatch?
Hi Christian,
we don’t clean this up, and the space being consumed is really minor since we speak about SQL scripts.
On the side, there is an effect to have it there: we don’t have to unzip the files.
But technically, you are right. Still, this would be a task for opatch, and not for datapatch. I can bring it to the opatches’ team’s attention – but I fear this won’t get treated since the clear trend is to promote (and default) out-of-place patching where this directory has no impact on your home since it contains only the apply and rollback scripts for the current installation, and nothing historical.
Cheers,
Mike
$ORACLE_HOME/sqlpatch folder is over 8GB on my system – can it be cleaned up manually?
Please use the “./datapatch -purge_old_metadata” call.
Don’t cleanup manually.
Open an SR, check with Support and send the SR number to me if they don’t find a solution.
Cheers,
Mike
Hi Mike, Oracle support pointed me to KB153775: “If above conditions are met, then $ORACLE_HOME/sqlpatch/ Folder and it’s contents can be cleared.”
Thanks – I added it to the blog post, and I did ask the datapatch team for advice as well, and why we are not cleaning this up by default.
Cheers,
Mike
Hi Mike,
I´ve seen recently released patch 37738908 (
SEPARATE PURGE_OLD_METADATA FROM PATCHING ACTIVITY IN DATAPATCH).
It sounds quite similar to 37352486 from patch description.
Can you please share some details about the differences?
thx
Andreas
Hi Andi,
patch 37352486 introduces the ability to cleanup with all the performance features – and makes it OPTIONAL.
Patch 37738908 now decouples it from patching.
I was not aware of this change but I guess the team is fully overtasked and forgot to tell me. Nevertheless, I will update the blog post.
See here from bug 37738908:
Currently,./datapatch -purge_old_metadata
will perform both patching and cleanup_activity.
purge_old_metadata involves cleanup of BLOB from datapatch internal tables.
This in some cases an I/O intensive operation, when there are huge number of pdbs and if the resources are limited.
So, to avoid any issue with patching, we would be disassociating/separating the cleanup activity from the patching flow.
With this txn,
./datapatch -purge_old_metadata
will perform only clean-up activity. It wouldn't patch.
Thanks for the hint!!
Cheers,
Mike
Hi Mike,
“This will trigger the cleanup. And you can decide when you want to do it. The fix is supposed to be included in the July 2025 Release Updates (19.28.0).”
It’s not included in 19.28.0, and there’s currently no patch for 19.28.0. Do you have any information about it?
Thx
Benny
Hi Benny,
it is not in 19.28 unfortunately. I updated the blog post.
The fix will be included in 19.29.
Thanks,
Mike
Hi Mike,
I just recently installed the 19.28 RU patch and did not see either bug fix 37352486 or 37738908 included in the $ORACLE_HOME/OPatch/opatch lsinventory -detail output. Are there plans to include the 37738908 bug fix in a Release Update or will it always be a One-off Patch?
Thanks,
James
Hi James,
deeply apologize – there was a final change, and it will make it into 19.29.
I did update the blog post …
Cheers,
Mike
Hi Mike.
37738908 doesn’t seem to be included in RU 19.28, no oneoff exist (yet, as of 2025-07-21), for 19.28.
Crazy, yes 🙁
I updated the blog post – it will be in 19.29 due to a last-minute change.
Cheers,
Mike
Hi Mike,
We wanted to wait with our cleanup until the corresponding patches were integrated into the DBRU patch. You indicated in February that this could be the case starting with 19.28. The MOS document for bug 37352486 now states: first included in 25.1.0.
Do you have any other information on this?
And if we apply the one-off patches now, doesn’t that mean that we’ll have to apply the respective one-off patches with every DBRU patch in the future?
Regards and thanks, Hans-Martin Ruff
Hi Hans-Martin,
i updated the blog post – it will be in 19.29. … thanks!
Mike
APR 2026 PSU re-released 5/16/2026
However, this patch was updated 5/17/2026 so might not be correct until JULY PSU
…
Applying 19.29 DB RU which has fix of Bug 37738908 does not reduce the REGISTRY$SQLPATCH_RU_INFO Size
KB813695
Last Updated
May 17, 2026
Service
Oracle Database – Enterprise Edition
LINK to May 17 2026 update – fix should be available in JULY PSU?
LINK TO ORACLE KB ON BUG FIX
Thanks – added it 🙂
Cheers,
Mike