Temp Tablespaces and Segments

[st2000]

Hello DBA's:
My query is failing due to Temp TS (extents getting over). Where can I check the total size allocated for the Tablespace. Checked v$sort_segment to find out that my Tablespace has 358016 total_extents. my db_block_size is 8k and so I presume that my Temp TS is 2.8GB. Checked other views (dba_data_files, dba_free_space, dba_extents) but could not find any info on Temp TS. I also want to find out what filesystem my Temp TS belongs to, before I add. The query eats up the Temp TS very fast (15 minutes) and fails.
Please advise.
Thanks ST2000

[pando]

look dba_temp_files

is this really *your* tablespace? seems like you dont even know how it was created

[st2000]

You are right Pando.. I have not created this database and I just started in this place.. If you look at original message, it shows as though Temp has 2.8GB, but truly (checking datafiles) I could see allocation of 4 datafiles equalling 3.8GB and I am wondering why 1 GB is not available..

Please look at my findings..

1 select substr(tablespace_name, 1,10) tablespace, substr(file_name, 1,30) file_name, file_id,
2 bytes, status, autoextensible, user_bytes, user_blocks
3* from dba_temp_files
SQL> /


TABLESPACE FILE_NAME FILE_ID BYTES STATUS AUT USER_BYTES USER_BLOCKS
---------- ------------------------------ ---------- ---------- --------- --- ---------- -----------
TEMP /u03/dbs/ODBMSPRD/temp01.dbf 1 1048576000 AVAILABLE NO 1047527424 127872
TEMP /u09/dbs/ODBMSPRD/temp02.dbf 2 AVAILABLE
TEMP /u07/dbs/ODBMSPRD/temp03.dbf 3 1048576000 AVAILABLE NO 1047527424 127872
TEMP /u12/dbs/ODBMSPRD/temp04.dbf 4 838860800 AVAILABLE NO 837812224 102272


For the fileID 2, it shows as available but no bytes shown..

From unix:

oracle@CG_JDA1:/u09/dbs/ODBMSPRD_> ls -l t*.dbf
-rw-r----- 1 oracle dba 1048584192 May 30 11:00 temp02.dbf

what does this mean? Where is the 1GB allocated to fileID 2.

Thanks, ST2000

[pando]

select * from v$tempfile;

[st2000]

Thanks Pando..
I see the file2 is offline..

SQL> alter database datafile '/u09/dbs/ODBMSPRD/temp02.dbf' online;
alter database datafile '/u09/dbs/ODBMSPRD/temp02.dbf' online
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile or tempfile '/u09/dbs/ODBMSPRD/temp02.dbf'

why would this happen ?

Thanks, ST2000

[pando]

it's tempfile not datafile

[st2000]

Sorry.. My command was for datafile, when it errored.. Now that I have it with tempfile instead of datafile it worked..

Thanks, ST2000

[pando]

by the way next time look file date as well, it indicates it has not been accesed since May