Temporary undo in Oracle 12c
In prior Oracle, the temporary table generated undo data to be stored in undo tablespace.
With this new feature in Oracle 12c R1 , the temporary table record stored in Temporary tablespace instead of undo teblespace.
1. Reduction in use of undo tablespace.
2. Less redo data generation.
Problems in pre Oracle:
1. Undo generated with global temporary tables increase the size which lead to more space needed to meet the undo retention period.
2. Temporary tables has transient data, which is not needed, so generating redo for temporary data add additional load on the system.
Enable Disable the Temporary undo
Note: For configure COMPATIBLE parameter is set to 12.0.0 or higher.
-- Session level
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;
-- System level
ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;
ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;
Check the Usage by enabling the parameter
-- Check temporary undo usage.
SET LINESIZE 200
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
FROM v$tempundostat ;
--Check undo used by transaction.
FROM v$transaction t,
WHERE s.saddr = t.ses_addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');