Who Are Using Temp Tablespace In Oracle

Buğra PARLAYAN

2 years ago

In today’s article, we will be learning Who Uses Temp Tablespace in Oracle Database.

We can find out using the following code block.

SELECT b.tablespace,
         ROUND ( ( (b.blocks * p.VALUE) / 1024 / 1024), 2) || 'M' AS temp_size,
         a.inst_id                                              AS Instance,
         a.sid || ',' || a.serial#                              AS sid_serial,
         NVL (a.username, '(oracle)')                           AS username,
         a.MACHINE,
         a.program,
         a.status,
         a.sql_id
    FROM gv$session a, gv$sort_usage b, gv$parameter p
   WHERE     p.name = 'db_block_size'
         AND a.saddr = b.session_addr
         AND a.inst_id = b.inst_id
         AND a.inst_id = p.inst_id
ORDER BY b.tablespace, b.blocks DESC

SELECT b.tablespace,

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

a.inst_id AS Instance,

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

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

a.MACHINE,

a.program,

a.status,

a.sql_id

FROM gv$session a, gv$sort_usage b, gv$parameter p

WHERE p.name = 'db_block_size'

AND a.saddr = b.session_addr

AND a.inst_id = b.inst_id

AND a.inst_id = p.inst_id

ORDER BY b.tablespace, b.blocks DESC