Temp Tablespace usage
Hi - We are in 12.2.0.1 Apr 2020 CPU patch in Exadata. We were doing the proactive monitoring and our DBA found that temp tablespace have gone upto 9TB.
I am using below query to check the temp tablespace size.
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