Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
1  
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

1 Answer 1

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

Your Answer

 
discard

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.