Printable View
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???
Is it possible that these are getting performed on the SYSTEM tablespace?
Nirasha
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.
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.