{"id":36,"date":"2011-05-06T19:56:10","date_gmt":"2011-05-06T19:56:10","guid":{"rendered":"http:\/\/tomas.papp.me.uk\/?p=36"},"modified":"2011-05-06T19:56:10","modified_gmt":"2011-05-06T19:56:10","slug":"oracle-temp-tablespace-usage","status":"publish","type":"post","link":"https:\/\/tomas.papp.me.uk\/?p=36","title":{"rendered":"Oracle TEMP tablespace usage"},"content":{"rendered":"<h4>Total used<\/h4>\n<pre>SELECT\u00a0\u00a0 A.tablespace_name tablespace, D.mb_total,\r\n SUM (A.used_blocks * D.block_size) \/ 1024 \/ 1024 mb_used,\r\n D.mb_total - SUM (A.used_blocks * D.block_size) \/ 1024 \/ 1024 mb_free\r\nFROM\u00a0\u00a0\u00a0\u00a0 v$sort_segment A,\r\n (\r\n SELECT\u00a0\u00a0 B.name, C.block_size, SUM (C.bytes) \/ 1024 \/ 1024 mb_total\r\n FROM\u00a0\u00a0\u00a0\u00a0 v$tablespace B, v$tempfile C\r\n WHERE\u00a0\u00a0\u00a0 B.ts#= C.ts#\r\n GROUP BY B.name, C.block_size\r\n ) D\r\nWHERE\u00a0\u00a0\u00a0 A.tablespace_name = D.name\r\nGROUP by A.tablespace_name, D.mb_total;<\/pre>\n<h4>By session<\/h4>\n<pre>SELECT\u00a0\u00a0 S.sid || ',' || S.serial# sid_serial, S.username,\r\n S.program, SUM (T.blocks) * TBS.block_size \/ 1024 \/ 1024 mb_used,\r\n COUNT(*) sort_ops\r\nFROM\u00a0\u00a0\u00a0\u00a0 v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P\r\nWHERE\u00a0\u00a0\u00a0 T.session_addr = S.saddr\r\nAND\u00a0\u00a0\u00a0\u00a0\u00a0 S.paddr = P.addr\r\nAND\u00a0\u00a0\u00a0\u00a0\u00a0 T.tablespace = TBS.tablespace_name\r\nGROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,\r\n S.program, TBS.block_size, T.tablespace\r\nORDER BY sid_serial;<\/pre>\n<h4>By SQL<\/h4>\n<pre>SELECT\u00a0\u00a0 S.sid || ',' || S.serial# sid_serial, S.username,\r\n T.blocks * TBS.block_size \/ 1024 \/ 1024 mb_used, T.tablespace,\r\n T.sqladdr address, Q.hash_value, Q.sql_text\r\nFROM\u00a0\u00a0\u00a0\u00a0 v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS\r\nWHERE\u00a0\u00a0\u00a0 T.session_addr = S.saddr\r\nAND\u00a0\u00a0\u00a0\u00a0\u00a0 T.sqladdr = Q.address (+)\r\nAND\u00a0\u00a0\u00a0\u00a0\u00a0 T.tablespace = TBS.tablespace_name\r\nORDER BY mb_used;<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Total used SELECT\u00a0\u00a0 A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) \/ 1024 \/ 1024 mb_used, D.mb_total &#8211; SUM (A.used_blocks * D.block_size) \/ 1024 \/ 1024 mb_free FROM\u00a0\u00a0\u00a0\u00a0 v$sort_segment A, ( SELECT\u00a0\u00a0 B.name, C.block_size, SUM (C.bytes) \/ 1024 \/ 1024 mb_total FROM\u00a0\u00a0\u00a0\u00a0 v$tablespace B, v$tempfile C WHERE\u00a0\u00a0\u00a0 B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE\u00a0\u00a0\u00a0 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-36","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/36","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=36"}],"version-history":[{"count":1,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/36\/revisions"}],"predecessor-version":[{"id":37,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/36\/revisions\/37"}],"wp:attachment":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=36"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=36"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}