Q

Necessary to take a cold backup of a temporary tablespace?

Is it necessary to take a cold backup of a temporary tablespace? According to me it is, but when we shut down the database the SMON process then cleans up the temporary tablespace also?

Is it necessary to take a cold backup of a temporary tablespace? According to me it is, but when we shut down the database the SMON process then cleans up the temporary tablespace also?

No, it is not necessary to take a cold backup of temporary tablespaces. Data in a temporary tablespace is temporary,

just like the name says. Data only lives in the temporary tablespace for the duration of the transaction. If you shut down the database, all transactions are ended (normally or abnormally). In either case, there are no transactions that require the contents of the temporary tablespace. When you start the database, SMON will clean up any data in the temporary tablespace as it is no longer needed.

If you restore from a cold backup and do not restore a TEMPORARY TABLESPACE (one that was created with the "CREATE TEMPORARY TABLESPACE ... TEMPFILE ...;" command) and the temporary tablespace is missing, then Oracle will re-create this for you automatically. If you created your Temp tablespace with the "CREATE TABLESPACE .... DATAFILE ... TEMP;" command, then Oracle will not recreate the tablespace for you on startup. In this case (which should no longer be used), you definitely should back up your temporary tablespace.

This was first published in January 2006

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close