Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My on-site DBA told me to create the particular table on temporary tablespace.Is there any advantage of such action?

share|improve this question
Not sure if tha'ts the same thing, but i.e. creating a global temporary table does not generate any redo or rollback information. If the data table you are creating does not need to be persistent, that's probably why your DBA told you to do that. – listik Aug 16 '13 at 11:02
A temporary tablespace is used by Oracle as scratch space for sorts that don't fit into memory, you cannot create a table there. A temporary table is a table whose rows are not kept when a transaction ends or when you logout. Either you misheard or your DBA is confused. – Gaius Nov 14 '13 at 15:19
You can. create table example (example number) tablespace TEMP; – Wouter Feb 12 '15 at 16:20
Have to correct myself here. You can create a table that is intent on using a certain tablespace, but can't add anything. (Only then actual segments are created). – Wouter Feb 13 '15 at 11:07

I believe your DBA told you to use temporary tablespace for temporary data? If so using temporary tables have the following advantages:

  • DML on temporary tables does not generate redo. If one uses direct path INSERT (INSERT /*+ append */, CTAS) then undo is also not generated. On 12c it is possible to store UNDO in temporary tablespace as well!
  • after session crash Oracle will take care to remove your data
  • after instance crash temporary tables do not require recover

Of course I forgot something. :) People will add more advantages in comments.

While listing advantages I am not considering scenario when you have to preserve data between restarts.

Database Concepts about Temporary Tables

share|improve this answer
There is a difference between "Global Temporary Tables" and "Temporary tablespaces using temporary datafiles". Your answer is unclear on what you're talking about, although i think most of the statements will be true for both. – Wouter Feb 12 '15 at 16:22

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.