Who is using which UNDO or TEMP segment ?

Undo tablespace is common for all the users for an instance  while temporary tablespace are assigned to users or a single default temporary tablespace is common for all users . To determine determine who is using a particular UNDO or Rollback Segment, use the bwlow query to find it .

SQL> SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser,
          s.program,  r.name undoseg , t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
          FROM sys.v_$rollname  r, sys.v_$session s, sys.v_$transaction t , sys.v_$parameter x
         WHERE s.taddr = t.addr AND  r.usn= t.xidusn(+)  AND  x.name  = 'db_block_size' ;
Output  :
SID_SERIAL    ORAUSER           PROGRAM                          UNDOSEG       Undo
--------------    ------------   ---------------------------------       -----------------   -------
260,7             SCOTT   sqlplus@localhost.localdomain       _SYSSMU4$     8K


To determine the user who is using a TEMP tablespace ,then fire the below query as :

SQL> SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
           a.sid||','||a.serial# SID_SERIAL , a.username, a.program 
           FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p
           WHERE p.name  = 'db_block_size'  AND a.saddr = b.session_addr
           ORDER BY b.tablespace, b.blocks; 
Output  :
TABLESPACE    SIZE    SID_SERIAL     USERNAME       PROGRAM
-----------------  -------  --------------      ----------------    --------------------------------
TEMP               24M       260,7                SCOTT        sqlplus@localhost.localdomain
                                     
 

Enjoy         :-)