DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: question about temp space

  1. #1
    Join Date
    Oct 2000
    Posts
    48
    The datafile for the temp tablespace was deleted in one of my test instances and I was going to offline drop the datafile, open the db, drop the temp tablespace and recreate it. But before doing that I realized I can still do distinct selects, sorts, and create table table_name as (select * from table table_name). How is this possible when the temp datafile doesn't even exist right now???

  2. #2
    Join Date
    Nov 2000
    Posts
    205
    Is it possible that these are getting performed on the SYSTEM tablespace?

    Nirasha
    Nirasha Jaganath

  3. #3
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    It uses the space allocated for sorting in SGA. Only after it is filled, then it will use Temp Tablespace. In your case most probably it is using the first one.
    Thanks
    Kishore Kumar

  4. #4
    Join Date
    Oct 2000
    Posts
    48
    Actually, Nirasha, you are right in that I was logged in as system doing these create statements in svrmgrl manager. But system's temporary tablespace is temp, so I still figured it would have been trying to use the temporary tablespace. Anyway, I got the following nasty errors in my alert log:

    < ORA-01171: datafile 14 going offline due to error advancing checkpoint
    < ORA-01116: error in opening database file 14
    < ORA-01110: data file 14: '/database/db01/ORACLE/FPRD/temp_FPRD_01.dbf'
    < ORA-27041: unable to open file
    < ORA-01110: data file 14: '/database/db01/ORACLE/FPRD/temp_FPRD_01.dbf'
    < ORA-01115: IO error reading block from file 14 (block # 1)
    < ORA-27041: unable to open file

    And now I can't do distinct select (even as system). I can still create tables as system though.

    Oh, well. I just found this interesting.

    Thanks.

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