Everyone says: Patch your software!
I keep saying: Patch your Oracle Database!
Let me tell you a little secret. All that patching is eating space in your SYSTEM tablespace…
This blog post is a continuation of Mike’s many blog posts on the topic. It adds an example with some real numbers.
Apply And Rollback Scripts
Normally, the apply and rollback scripts are stored in the Oracle home. Here’s an example:
$ ll $ORACLE_HOME/sqlpatch
drwxr-xr-x. 4 oracle oinstall 38 Apr 18 2019 29517242
drwxr-xr-x. 3 oracle oinstall 22 Jul 28 05:28 36878697
drwxr-xr-x. 3 oracle oinstall 22 Jul 28 05:27 36912597
drwxr-xr-x. 3 oracle oinstall 22 Jul 28 05:30 37056207
drwxr-xr-x. 3 oracle oinstall 22 Aug 11 08:34 37102264
drwxr-xr-x. 3 oracle oinstall 22 Aug 11 08:32 37260974
drwxr-xr-x. 3 oracle oinstall 22 Aug 11 08:36 37470729
drwxr-xr-x. 3 oracle oinstall 22 Aug 11 08:47 37499406
drwxr-xr-x. 3 oracle oinstall 22 Aug 11 08:44 37642901
drwxr-xr-x. 3 oracle oinstall 22 Aug 11 08:49 37777295
When Datapatch applies patches to your Oracle Database, it uses the apply and rollback scripts from the Oracle home.
But during apply, Datapatch also takes the rollback scripts and stores them inside the database – in the SYSTEM tablespace. This ensures that Datapatch can always roll back patches, regardless of whether the rollback scripts are in the Oracle home.
When you use the OPatch cleanup or uses out-of-place patching, there is a risk that Datapatch might need a rollback script which is no longer found in the Oracle home. But then Datapatch simply finds it in the SYSTEM tablespace instead.
This avoild a lot of chaotic situations with missing rollback scripts.
Storing The Scripts
Datapatch uses two tables that both have a column named PATCH_DIRECTORY:
select * from dba_registry_sqlpatch;
select * dba_registry_sqlpatch_ru_info;
This query shows each patch action and corresponding usage for apply/rollback script:
select * from (
select description, round(dbms_lob.getlength(PATCH_DIRECTORY)/1024/1024, 2) as size_mb
from dba_registry_sqlpatch
where action='APPLY' and description not like 'Database Release Update%'
union
select 'Release Update ' || RU_version as description, round(dbms_lob.getlength(PATCH_DIRECTORY)/1024/1024) as size_mb
from dba_registry_sqlpatch_ru_info)
order by description;
Here’s an example of an Oracle Database that I have patched a few times.
DESCRIPTION SIZE_MB
------------------------------------------------- -------
DATAPUMP BUNDLE PATCH 19.25.0.0.0 1.03
DATAPUMP BUNDLE PATCH 19.26.0.0.0 1.03
DATAPUMP BUNDLE PATCH 19.27.0.0.0 1.03
DATAPUMP BUNDLE PATCH 19.28.0.0.0 1.04
OJVM RELEASE UPDATE: 19.25.0.0.241015 (36878697) .01
OJVM RELEASE UPDATE: 19.26.0.0.250121 (37102264) .02
OJVM RELEASE UPDATE: 19.27.0.0.250415 (37499406) .02
OJVM RELEASE UPDATE: 19.28.0.0.250715 (37847857) .02
Release Update 19.25.0.0.0 175
Release Update 19.26.0.0.0 184
Release Update 19.27.0.0.0 194
Release Update 19.28.0.0.0 203
Release Update 19.3.0.0.0 4
10 rows selected.
That’s around 750 MB.
Cleaning Up
Datapatch only needs the rollback scripts for the patches that are currently applied. You can remove all other scripts:
$ORACLE_HOME/OPatch/datapatch -purge_old_metadata
Using the above environment, this is the result of the cleanup:
DESCRIPTION SIZE_MB
------------------------------------------------- -------
DATAPUMP BUNDLE PATCH 19.25.0.0.0
DATAPUMP BUNDLE PATCH 19.26.0.0.0
DATAPUMP BUNDLE PATCH 19.27.0.0.0
DATAPUMP BUNDLE PATCH 19.28.0.0.0 1.04
OJVM RELEASE UPDATE: 19.25.0.0.241015 (36878697)
OJVM RELEASE UPDATE: 19.26.0.0.250121 (37102264)
OJVM RELEASE UPDATE: 19.27.0.0.250415 (37499406)
OJVM RELEASE UPDATE: 19.28.0.0.250715 (37847857) .02
Release Update 19.25.0.0.0
Release Update 19.26.0.0.0
Release Update 19.27.0.0.0
Release Update 19.28.0.0.0 203
Release Update 19.3.0.0.0
10 rows selected.
Datapatch now only uses little more than 200 MB.
A few comments about the cleanup functionality:
- It is available via patch 37738908. Hopefully, it will be part of the 19.29 Release Update.
- You can safely execute the cleanup. Datapatch doesn’t remove scripts that it might need at a later point.
- Oracle recommends that you run the cleanup in an off-peak period.
- The cleanup happens via a
TRUNCATE TABLEcommand which effectively reclaims space so other segments may use it. However, it doesn’t shrink the tablespace, so the physical size of the data files remain the same. - To facilitate the
TRUNCATE TABLEcommand, those records that must remain is copied to a new table. After truncating the original table, those records are moved back and the temporary table is dropped. This might lead to a little increase in space usage while Datapatch cleans up. - When you upgrade, the upgrade engine truncates those tables. They are of no use following an upgrade.
Final Words
- The more you patch, the bigger the problem. Each patch iteration stores the rollback scripts.
- The more PDBs you have, the bigger the problem because Datapatch stores the rollback scripts in each PDB that it patches.
- If you want a little more details read Mike’s blog posts:
Check your Oracle Database. How much space does Datapatch use? What was the largest amount of space you could reclaim? Let me know in the comments below.
Update
- 03-SEP-2025: I correctly wrote that the tables use the SYSAUX tablespace. They use the SYSTEM tablespace. Also, I added a detail about the cleanup might take up a little more space temporarily. Thanks to Pete for letting me know.
Databases Are Fun 