Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: