Difference between revisions of "Show temp tablespace usage"
Jump to navigation
Jump to search
(Created page with "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.u...") |
|||
| Line 1: | Line 1: | ||
| + | <source lang = "sql"> | ||
SELECT b.tablespace, | SELECT b.tablespace, | ||
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size, | ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size, | ||
| Line 7: | Line 8: | ||
a.status, | a.status, | ||
a.sql_id | a.sql_id | ||
| − | FROM | + | FROM gv$session a, |
gv$sort_usage b, | gv$sort_usage b, | ||
gv$parameter p | gv$parameter p | ||
| − | WHERE | + | WHERE p.name = 'db_block_size' |
| − | AND | + | AND a.saddr = b.session_addr |
| − | AND | + | AND a.inst_id=b.inst_id |
| − | AND | + | AND a.inst_id=p.inst_id |
| − | ORDER BY b.tablespace, b.blocks | + | ORDER BY b.tablespace, b.blocks; |
| − | / | + | </source> |
Latest revision as of 14:26, 28 April 2016
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.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;