Managing Temporary Undo in Oracle 12c

Managing Temporary Undo in Oracle 12.1

Temporary table used same process to used the default undo tablespace like a permanent table in Oracle Database and generate redo logs for operation which little bit effects on performance.

From 12c, You can also create a separate temporary undo for temporary tables in Oracle Database and not generate redo logs.

Benefits:
–Temporary undo reduce the undo data in permanent undo tablepsace which helps undo to maintain realistic data as undo retention parameter.
–It effects on performance because less redo is generated.

Note: You can enable temporary undo for a specific session or system level.

Enable and Disable the Temporary undo

--Enable temporary undo for a session
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

--Disable temporary undo for a session
ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;

--Enable temporary undo for the system
ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

--Disable temporary undo for the system
ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;

Check the status of Undo Space Transaction

-- monitoring and tuning the undo space
select * from V$undostat;

-- monitoring and tuning the temporary undo space
select * from V$TEMPUNDOSTAT;

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 )

w

Connecting to %s

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