Posted on 2011-10-24
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
    LVL 73

    Assisted Solution

    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

    - 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

    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?
    LVL 73

    Expert Comment

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

    Author Comment

    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 73

    Expert Comment

    no you can do that on a live system

    Author Closing Comment

    Excellent answers.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    This video shows how to recover a database from a user managed backup
    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.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now