My Oracle Support Banner

How To Resize Temp-tablespace In Active Data Guard Scenario (Doc ID 2102253.1)

Last updated on JUNE 22, 2021

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.


Management of TEMP TABLESPACE in ADG is very important from 12c because DML on TEMP is allowed in ADG.

DML changes to TEMP doesn't generate REDO because it is TEMP tablespace. However undo generated for the changes does in turn generate REDO. Due to this reason GLOBAL TEMP Tablespace is not allowed in ADG. However from 12c onwards UNDO for global TEMP tablespace is stored in TEMP itself unlike UNDO tablespace.Due to the above reason it is very much important to look for an option to resize the TEMP properly.


++ Read mostly application can redirect their table sorting with GLOBAL TEMP TABLES on ADG.
++ Use TEMP_UNDO_ENABLED to TRUE to enable TEMP UNDO on primary. However in ADG it is default enabled.
++ We can use this TEMP_UNDO_ENABLED on primary to reduce the REDO generation for TEMP operation


Resize Temp on ADG failing with ORA-16000


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.