Monitoring the Space Usage in TEMPORARY tablespace

Temporary tablespace is used for all the sorting operations. If the temporary tablespace is not sufficient enough, then you will get the ' unable to extend temp segment by %s bytes' error.
If you want to Monitor the Space Utilization in TEMPORARY tablespace proactively to avoid the space issues then follow the process mentioned below to effectively monitor the TEMPORARY space utilization

1. Find the Temporary tablespace names and the block size using the following SQL

SQL> select tablespace_name,block_size from dba_tablespaces where tablespace_nam
e like '%TEMP%';

TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
TEMP 8192

From the above command it is clear that we have only one TEMPORARY tablespace 'TEMP' withe the block size as 8192

2. Find out what is the total size of the TEMPORARY segment and how much space being utilized and how much free space is left for the future sort operations using the following SQL ( calculated with the 8192 block size)

SQL> select tablespace_name,total_blocks*8192/1024/1024 as total_MB,
2 used_blocks*8192/1024/1024 used_MB,
3 free_blocks*8192/1024/1024 free_MB from v$sort_segment;

TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB
------------------------------- ---------- ---------- ----------
TEMP 44 0 44

From the above output
Total size is 44 MB
Used is 0 MB
Free space 44 MB

Make sure that there is always a free space available to accommodate the future sort operations.
3. If you want to know who is using the temporary tablespace at the moment , then run the following SQL to know who is using the temporary tablespace.

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

SQL>

TABLESPACE SEGFILE# SEGBLK# BLOCKS SID
------------------------------- ---------- ---------- ---------- ----------
SERIAL# USERNAME OSUSER
---------- ------------------------------ ------------------------------
STATUS
--------
TEMP 201 5513 5632 144
170 DEMO US\smarampelly
ACTIVE