How should I determine the available free space in temporary tablespace ?
I encountered the error ORA-01652: unable to extend temp segment by 640 in tablespace TEMP in the alert log.
DB version : 9.2.0.8.0 on Windows 2000 server
So, I query to see the available free space in tablespace TEMP (temporary) as below :
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size