Temporary undo in Oracle 12c

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.

Benefit:
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';
SELECT *
FROM v$tempundostat ;

--Check undo used by transaction.
SELECT t.used_ublk,
t.used_urec
FROM v$transaction t,
v$session s
WHERE s.saddr = t.ses_addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.