About Me

My photo
Pune, Maharashtra, India
Working on Oracle technology for more than 14+ years.Oracle Certified professional. Expertise in Oracle advance technologies like Oracle Real Application Cluster (RAC) , Oracle DataGuard, Oracle ASM, Oracle Golden gate. Experience in Area of migration/Replication of Oracle Database. Expertise in Storage/OS administration,Vertulization, Cloud technology. Activly involed in forum discussion of RAC sig and DBA village.

Monday, May 30, 2011

Temporary tablespace usage for each user

COLUMN tablespace FORMAT A20

COLUMN temp_size FORMAT A20

COLUMN sid_serial FORMAT A20

COLUMN username FORMAT A20

COLUMN program FORMAT A40

SET LINESIZE 200



SELECT b.tablespace,

ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,

a.sid||','||a.serial# AS sid_serial,

NVL(a.username, '(oracle)') AS username,

a.program

FROM v$session a,

v$sort_usage b,

v$parameter p

WHERE p.name = 'db_block_size'

AND a.saddr = b.session_addr

ORDER BY b.tablespace, b.blocks;