DB version: 11.2.0.4
Platform : Oracle Linux 6.4
It seems that finding the space left in temporary tablespace is not straightforward.
Question1.
These are the column descriptions for DBA_TEMP_FREE_SPACE view
ALLOCATED_SPACE: Total allocated space, in bytes, including space that is currently allocated
and used and space that is currently allocated and available for reuse
FREE_SPACE : Total free space available, in bytes, including space that is currently allocated and available for
reuse and space that is currently unallocated
http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5062.htm#REFRN23627
I am not a native English speaker, but , aren't the words in red above for ALLOCATED_SPACE column an erroneous repetition ?
Question2. In 10g, when we see zero space left in Temp tablespace, we used to add more space to TEMP tablespace straightaway to prevent ORA-1652: unable to extend temp segment .In 11g, is there an architectural change by which TEMP segments are reused internally (like UNDO segments) so that we don't have to worry much when we see 0 space left in temp tablespace ?
Question3. For the same database, Outputs from two queries are listed below. The freespace info varies between QueryA and QueryB.
Which is the correct output ; QueryA or QueryB ?
--- QueryA using dba_temp_free_space
select TABLESPACE_NAME ,
tablespace_size/1024/1024/1024 totalGB ,
ALLOCATED_SPACE/1024/1024/1024 AllocSpaceGB ,
free_space/1024/1024/1024 FreeSpaceGB
from dba_temp_free_space ;
TABLESPACE_NAME TOTALGB ALLOCSPACEGB FREESPACEGB
------------------------------ ---------- ------------ -----------
TEMP 125.99707 125.748047 69.9150391
--- QueryB using v$sort_segment
SELECT A.tablespace_name tablespace, D.gb_total,
SUM (A.used_blocks * D.block_size)/1024/1024/1024 gb_used,
D.gb_total - SUM (A.used_blocks * D.block_size)/1024/1024/1024 gb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes)/1024/1024/1024 gb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts# = C.ts#
GROUP BY B.name, C.block_size
) D
WHERE
A.tablespace_name = D.name
GROUP by A.tablespace_name, D.gb_total;
TABLESPACE GB_TOTAL GB_USED GB_FREE
------------------------------- ---------- ---------- ----------
TEMP 125.99707 22.6992188 103.297852