Subscribe to Posts by Email

Subscriber Count

    701

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

Administration : Undo stores in Temporary tablespace?

Yes for for Temporary Tables.

Here is the excerpt from the documentation:-

By default, undo records for temporary tables are stored in the undo tablespace and are logged in the redo, which is the same way undo is managed for persistent tables. However, you can use the TEMP_UNDO_ENABLED initialization parameter to separate undo for temporary tables from undo for persistent tables. When this parameter is set to TRUE, the undo for temporary tables is called temporary undo.

Benefits

	Temporary undo reduces the amount of undo stored in the undo tablespaces.

	Less undo in the undo tablespaces can result in more realistic undo retention period requirements for undo records.
	Temporary undo reduces the size of the redo log.

	Performance is improved because less data is written to the redo log, and components that parse redo log records, such as LogMiner, perform better because there is less redo data to parse.

	Temporary undo enables data manipulation language (DML) operations on temporary tables in a physical standby database with the Oracle Active Data Guard option. However, data definition language (DDL) operations that create temporary tables must be issued on the primary database.

You can enable temporary undo for a specific session or for the whole system

Set the TEMP_UNDO_ENABLED initialization parameter:

	To enable temporary undo for a session, run the following SQL statement:
	ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;



	To disable temporary undo for a session, run the following SQL statement:
	ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;


	To enable temporary undo for the system, run the following SQL statement:
	ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

	After temporary undo is enabled for the system, a session can disable temporary undo using the ALTER SESSION statement.


	To disable temporary undo for the system, run the following SQL statement:
	ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;

After temporary undo is disabled for the system, a session can enable temporary undo using the ALTER SESSION statement.

Note: By default the temporary undo is enabled for dataguard

Views:- V$TEMPUNDOSTAT,

Comments are closed.