Temp tablespace status in Oracle 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Temp tablespace status in Oracle 9i

  1. #1
    Join Date
    May 2002
    Posts
    163

    Temp tablespace status in Oracle 9i

    Hi,

    In my oracle V9R2, I temp file got deleted from O/S level. But I startup my database successfully.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 135338868 bytes
    Fixed Size 453492 bytes
    Variable Size 109051904 bytes
    Database Buffers 25165824 bytes
    Redo Buffers 667648 bytes
    Database mounted.
    Database opened.

    And when I am querying v$tempfile it is showing the details, but when I am querying dba_temp_files , I am getting the expected following error

    ERROR at line 1:
    ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
    ORA-01110: data file 201: 'E:\ORACLE\ORA92\BUCHKU\TEMP01.DBF'


    But I wonder how Oracle allows me to startup my database successfully. Can anybody give me some details on this? I will appreciate.
    Nwcomer
    Student

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    If you know the purpose or what role a temporary tablespace plays in a database then you have the answer.

    A temporary tablespace is used only to create temporary segments. It doesnot play any role in database startup or shutdown...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    May 2002
    Posts
    163
    Thaks for your clarification. I knew that, but I was confused bcoz I was getting error in Oracle 8i while starting up my database.

    I feel the difference is HOW I HAVE created those tablespaces. In 8i I created a Temp tablespace with a data file. I did not create a temp file. While in 9i I created with Tempfile clause.

    Will you agree with me? Or rectify me.

    Thanks
    Nwcomer
    Student

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    If tempfile does not exist when the database is brought up, DBWR writes to a trace file indicating the tempfile is not found, but the database opens normally.

    You can offline drop those tempfiles, drop the tablespace and create a new one.

    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    considering itīs a temp tablespace and tempfiles I dnt think need to recreate the tablespace when you lose a tempfile, I believe you simply add a tempfile to the temp tablespace (9i)

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by pando
    considering itīs a temp tablespace and tempfiles I dnt think need to recreate the tablespace when you lose a tempfile, I believe you simply add a tempfile to the temp tablespace (9i)
    You are right pando, no need to drop tablespace.
    Just do this.
    Code:
    alter database tempfile 'E:\ORACLE\ORA92\BUCHKU\TEMP01.DBF' drop;
    alter tablespace temp add tempfile 'E:\ORACLE\ORA92\BUCHKU\TEMP01.DBF' size 10m;
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width