Query related to TEMP Tablespace.

Query to check TEMP Table-space Size, Used Space.

SELECT d.status “Status”, d.tablespace_name “Name”, d.contents “Type”, d.extent_management”ExtManag”,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),’99,999,990.900′) “Size (M)”, TO_CHAR(NVL(t.bytes,
0)/1024/1024,’99999,999.999′) ||’/’||TO_CHAR(NVL(a.bytes/1024/1024, 0),’99999,999.999′) “Used (M)”,
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), ‘990.00’) “Used %”
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like ‘LOCAL’ AND d.contents like ‘TEMPORARY’;

Query to get DATAFILE Location & Details of TEMP Tablespace.

select tablespace_name,file_name,bytes/1024/1024 Total_Size,maxbytes/1024/1024 Max_Space,autoextensible from dba_temp_files  order by file_name;

Query to list all temp-files of TEMP Tablespace.

SELECT d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id, d.bytes/1024/1024
size_m, NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v
WHERE (t.file_id (+)= d.file_id)
AND (d.file_id = v.file#);