1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-25153: Temporary Tablespace is Empty

Discussion in 'SQL PL/SQL' started by UnixGuru, Dec 15, 2008.

  1. UnixGuru

    UnixGuru Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    hi, While creating an external table in oracle database I get the below error message:

    Code (Text):
    ORA-25153: Temporary Tablespace is Empty
    I searched this on google and mostly people say that I have to create a data file in temporary tablespace. however my point is that In Oracle it is not possible to create a tablespace without adding a datafile.
     
  2. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    25153, 00000, "Temporary Tablespace is Empty"
    // *Cause: An attempt was made to use space in a temporary tablespace with
    // no files.
    // *Action: Add files to the tablespace using ADD TEMPFILE command.


    The tempfiles somehow go deleted. recreate it. Did you do cloning recently?
     
  3. UnixGuru

    UnixGuru Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    hi simply_dba thanks for your reply, i had re-created the control file for some other dba activity and i believe whenever one recreates the control file, oracle ignores the current temp datafile.

    That was y i was getting the "ORA-25153: Temporary Tablespace is Empty" error. I relaized this and added the tempfile properly with reuse option.

    Do correct me if i am wrong in my logic?
     
  4. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Right
    Recreating control files would render the present temp files orphan as their information is not included while control file is being created. You have to explicitely create the temp files like the way you did.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This Link may provide you some valuable information .