Patching Eats Your SYSTEM Tablespace

 

Why Oracle Patching Can Fill Up SYSAUX and SYSTEM Tablespaces

When you apply Release Updates (RUs) using datapatch, Oracle doesn’t just update binaries. It also stores rollback and apply scripts inside the database itself. These are saved as LOBs in registry tables, which means the SYSAUX or SYSTEM tablespace may steadily grow over time. For DBAs managing long-lived environments or multiple PDBs, this hidden space consumption can become noticeable.

---

Where Are These Scripts Stored?

The metadata lives inside dictionary views such as:

• DBA_REGISTRY_SQLPATCH
• DBA_REGISTRY_SQLPATCH_RU_INFO

The column PATCH_DIRECTORY in particular holds rollback/apply scripts. Since these are LOBs, their size can reach hundreds of MB for each RU cycle.

---

Checking the Size of Datapatch Metadata

The following query lists the patch description and its LOB size in MB:

SQL> select description,
            round(dbms_lob.getlength(patch_directory)/1024/1024,2) size_mb
     from   dba_registry_sqlpatch
     order by size_mb desc;

DESCRIPTION                                               SIZE_MB
--------------------------------------------------------  -------
Release_Update 19.20.0.0.240716                           245.13
Release_Update 19.19.0.0.240420                           243.11
Release_Update 19.18.0.0.240116                           243.01
...

You can also view information for Release Updates specifically:

SQL> select 'RU ' || ru_version description,
            round(dbms_lob.getlength(patch_directory)/1024/1024,2) size_mb
     from   dba_registry_sqlpatch_ru_info;

DESCRIPTION         SIZE_MB
-----------------   -------
RU 19.20.0.0.240716 245.13
RU 19.19.0.0.240420 243.11
RU 19.18.0.0.240116 243.01
...

To check total usage across all entries:

SQL> select round(sum(dbms_lob.getlength(patch_directory))/1024/1024,2) total_mb
     from   dba_registry_sqlpatch;

TOTAL_MB
--------
974.23

In this example, nearly 1 GB of tablespace is tied up by stored patch scripts.

---

How to Reclaim Space

Oracle offers a cleanup feature built into datapatch. It removes rollback/apply scripts that are no longer needed:

$ORACLE_HOME/OPatch/datapatch -purge_old_metadata

Internally, the purge process works like this:

• It copies required rows into a temporary holding table.
• The registry table is truncated.
• Valid rows are reinserted, discarding unnecessary metadata.

The result is that logical space is freed within the tablespace. Keep in mind that this does not shrink the physical datafile on disk — it only makes space available for reuse within the tablespace itself.

---

Example: Before and After Purge

Before purge:

DESCRIPTION                       SIZE_MB
--------------------------------- -------
Release_Update 19.20.0.0.240716   245.13
Release_Update 19.19.0.0.240420   243.11
Release_Update 19.18.0.0.240116   243.01

TOTAL_MB                          974.23

After purge:

DESCRIPTION                       SIZE_MB
--------------------------------- -------
Release_Update 19.20.0.0.240716   245.13

TOTAL_MB                          245.13

The cleanup removed older rollback metadata, leaving only the latest RU, and reduced usage by ~730 MB in this case.

---

Considerations for Multitenant

If you use a CDB with multiple PDBs, each PDB stores its own patch metadata. That means tablespace usage scales with the number of PDBs. You’ll need to run cleanup inside each PDB to reclaim space everywhere.

---

Important Notes

• Safety: The purge only deletes metadata that is no longer required. Active rollback information is preserved.
• Timing: Because the process uses a truncate and temporary table copy, schedule it in a maintenance window or low-activity period.
• Datafiles: Even after purge, the physical size of SYSTEM or SYSAUX datafiles won’t shrink automatically. If you need to reduce actual file size, you must follow standard datafile resize/shrink procedures.
• PDBs: Run the cleanup in each PDB individually to reclaim space there.

---

Monitoring Queries

To prevent surprises, you can regularly check patch metadata size. Here are some useful scripts:

Largest entries:

set pages 200 lines 150
col description format a60

select description,
       round(dbms_lob.getlength(patch_directory)/1024/1024,2) size_mb
from   dba_registry_sqlpatch
order  by size_mb desc;

Total usage:

select round(sum(dbms_lob.getlength(patch_directory))/1024/1024,2) total_mb
from   dba_registry_sqlpatch;

Tablespace usage:

select df.tablespace_name,
       df.bytes/1024/1024 total_mb,
       (df.bytes - nvl(fs.free_bytes,0))/1024/1024 used_mb,
       nvl(fs.free_bytes,0)/1024/1024 free_mb
from   (select tablespace_name, sum(bytes) bytes 
        from dba_data_files 
        group by tablespace_name) df
left join
       (select tablespace_name, sum(bytes) free_bytes 
        from dba_free_space 
        group by tablespace_name) fs
on df.tablespace_name = fs.tablespace_name;

---

Final Thoughts

Patching is essential, but hidden growth in SYSTEM and SYSAUX can create pressure on tablespaces if not monitored. By running the size queries before and after each patch cycle and using datapatch -purge_old_metadata, you can keep metadata overhead under control. Add these checks to your DBA toolkit to avoid sudden tablespace alerts after patching.

---

Reference: Inspired by the article at dohDatabase.com (Sept 2025).