Oracle Temporal tablespace Usage Analysis

To query the usage of temporary tablespace:

SELECT * FROM (select A.tablespace_name,sum (maxbytes/1024/1024/1024) total_g,sum (a.bytes/1024/1024) ALLOCATED_MB
From Dba_temp_files A
where A.tablespace_name=upper (' XXX ') group by a.tablespace_name) x,
(select sum (b.bytes_used/1024/1024) USED_MB,
SUM (b.bytes_free/1024/1024) FREE_MB
From V$temp_space_header b
where B.tablespace_name=upper (' XXX ') group by b.tablespace_name) y;

Query the SQL statement that occupies the temporary tablespace in the existing run:

Select se.sid,se.username,su.blocks*ts.block_size/1024/1024 Mb_used,su.tablespace,
Su.sqladdr Address,sq.hash_value,sq.sql_text
From V$sort_usage su,v$session se,v$sqlarea sq,dba_tablespaces ts
where su.session_addr=se.saddr
and su.sqladdr=sq.address (+)
and Su.tablespace=ts.tablespace_name;

Query session summary, usage of temporary tablespace:

Select Se.sid,se.username,se.osuser,pr.spid,se.module,se.program,
SUM (su.blocks)ts.block_size/1024/1024 Mb_used,su.tablespace,
Count () sorts
From V$sort_usage su,v$session se,dba_tablespaces ts,v$process PR
where su.session_addr=se.saddr
and SE.PADDR=PR.ADDR
and Su.tablespace=ts.tablespace_name
Group BY Se.sid,se.username,se.osuser,pr.spid,se.module,se.program,ts.block_size,su.tablespace;

---Note sometimes we see unable to extend temp segment by 1024x768 in Tablespace AppData;
Not carefully thought is the temporary table space is insufficient, actually here should see AppData table space size

Oracle Temporal tablespace Usage Analysis