SYSAUX Tablespace Grows Quite Fast Due to Apply Spilling (Doc ID 556183.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.3 to 11.1.0.6


Symptoms

SYSAUX tablespace is growing uncontrollably and no updates are being propagated from source to target

We can find out the transaction ID on the TARGET side from the healthcheck section titled:

++ APPLY SPILLED TRANSACTIONS ++

No matter how many times this transaction is set to "_ignore_transaction" and purged on the
TARGET  site, the Source site will continue to send this transaction over and it will show up in this
section. The way to clear up this transaction is to issue the "_ignore_transaction" on the
source and then issue the "_ignore_transaction" and sys.purge_spill_txn on the TARGET. An example is
followed below:

OnTarget's healthcheck report, the ++ APPLY SPILLED TRANSACTIONS ++ section shows the following  transaction ID: 3.30.13393

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms