question about temp space

[lake612]

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

[nirasha]

Is it possible that these are getting performed on the SYSTEM tablespace?

Nirasha

[Kishore]

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.

[lake612]

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.