Who Are Using Temp Tablespace In Oracle

Buğra PARLAYAN February 20, 2023 ORACLE

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

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

Who Are Using Temp Tablespace In Oracle

About Buğra PARLAYAN

Leave a Reply Cancel reply