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 :-)
No comments:
Post a Comment