-
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
-
look dba_temp_files
is this really *your* tablespace? seems like you dont even know how it was created :D
-
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
-
select * from v$tempfile;
-
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
-
it's tempfile not datafile
-
Sorry.. My command was for datafile, when it errored.. Now that I have it with tempfile instead of datafile it worked..
Thanks, ST2000
-
by the way next time look file date as well, it indicates it has not been accesed since May :D
