Version: 10.2.0.4
Platform : RHEL 5.8
Currently we are running a batch job in a schema. The default temporary tablespace for that schema is TEMP.
But I see that the tablespace is full.
SQL> select file_name, bytes/1024/1024/1024 gb from dba_temp_files where tablespace_name = 'TEMP';
FILE_NAME GB
----------------------------------------------------------------- ----------
/prd/fdms/oradata_db18/fdmsc1sdb/oradata/ts_temp/temp01.dbf 10
SQL> SELECT TABLESPACE_NAME, FILE_ID,
BYTES_USED/1024/1024,
bytes_free/1024/1024
FROM V$TEMP_SPACE_HEADER where tablespace_name = 'TEMP' 2 3 4 ;
TABLESPACE_NAME FILE_ID BYTES_USED/1024/1024 BYTES_FREE/1024/1024
---------------------------------------- ---------- -------------------- --------------------
TEMP 1 10240
So, far the application users have not complained and I didn't see any 'unable to extend' error in the alert log yet, but the above scenario is dangerous. Right? I mean SQL statements with sorting can error out. Right ? Unlike UNDO, with temp tablespace, temp segments cannot be reused. Right ?