[Webinar] Learn how to a build a cloud-first strategyRegister Now



Posted on 2011-10-24
Medium Priority
Last Modified: 2012-05-12
Is the TEMP data file mandatory in oracle? what problems that can happen if you dont have one.

I noticed one DB had a TEMP tablespace but there was no data file assigned or were deleted.

would this create issue or all SORTS and JOINS happenin the SGA.

What is the standard size or command for creating the TEMP file if i must do it.
Question by:sam15
  • 3
  • 3
LVL 74

Assisted Solution

sdstuber earned 1000 total points
ID: 37022167
temp "can" be built on datafiles  (found in dba_data_files)

however, oracle recommendation (since 8i)  is to use "tempfiles"  (found in dba_temp_files)

you must have at least one TEMP tablespace, and that tablespace must have at least one datafile or at least one tempfile.

you can't mix datafiles and tempfiles.  Again, you "should" use tempfiles
LVL 23

Accepted Solution

OP_Zaharin earned 1000 total points
ID: 37022183
- in certain condition such as sorting on million of records is too large for the memory to take. therefore Oracle need to use the disk which is the TEMP tablespace.
- if all TEMP tablespace is dropped, then the SYSTEM tablespace will be used as the default temporary tablespace.

- the following link should explain in further detail of what TEMP tablespace is used for and the command to create it:

Author Comment

ID: 37022209
there is a TEMP tablespae but there is no temp data files assigned to it. it sounds to me someone deleted it or something is wrong.

But, if i am correct, if the DB has SYSTEM tablespace locally managed we *must* have a temp file and it wont use SYSTEM for temp operations and many problems will happen. right?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 74

Expert Comment

ID: 37022216
 can't guarantee no problems with "real" temp but you definitely want to avoid using SYSTEM tablespace as your TEMP

Author Comment

ID: 37022294
so i should run this statement first
ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;

do i need to bounce the instance or database or not?

LVL 74

Expert Comment

ID: 37022296
no you can do that on a live system

Author Closing Comment

ID: 37029056
Excellent answers.

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question