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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam15Author Commented:
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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 can't guarantee no problems with "real" temp but you definitely want to avoid using SYSTEM tablespace as your TEMP
sam15Author Commented:
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?

no you can do that on a live system
sam15Author Commented:
Excellent answers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.