{"id":1425,"date":"2019-04-12T10:34:45","date_gmt":"2019-04-12T09:34:45","guid":{"rendered":"https:\/\/dbafox.com\/?p=1425"},"modified":"2019-04-12T10:35:08","modified_gmt":"2019-04-12T09:35:08","slug":"oracle-using-ash-to-monitor-temporary-tablespace-usage","status":"publish","type":"post","link":"https:\/\/dbafox.com\/?p=1425","title":{"rendered":"Oracle &#8211; Using ASH to Monitor Temporary Tablespace Usage"},"content":{"rendered":"<h1>Using ASH to Monitor Temporary Tablespace Usage<\/h1>\n<p>To use ASH (Active Session History) to monitor temporary tablespace usage use SQL similar to the query below. Detect the space used by queries.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT ASH.inst_id,\r\n  ASH.user_id,\r\n  ASH.session_id sid,\r\n  ASH.session_serial# serial#,\r\n  ASH.sql_id,\r\n  ASH.sql_exec_id,\r\n  ASH.sql_opname,\r\n  ASH.module,\r\n  MIN(sample_time) sql_start_time,\r\n  MAX(sample_time) sql_end_time,\r\n  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24) etime_secs ,\r\n  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (60*24) etime_mins ,\r\n  MAX(temp_space_allocated)\/(1024*1024) max_temp_mb\r\nFROM gv$active_session_history ASH\r\nWHERE ASH.session_type = 'FOREGROUND'\r\nAND ASH.sql_id        IS NOT NULL\r\n--AND sample_time BETWEEN to_timestamp('01-04-2019 10:00', 'DD-MM-YYYY HH24:MI') AND to_timestamp('13-04-2019 12:00', 'DD-MM-YYYY HH24:MI')\r\nAND sample_time &gt; systimestamp - 3\r\nGROUP BY ASH.inst_id,\r\n  ASH.user_id,\r\n  ASH.session_id,\r\n  ASH.session_serial#,\r\n  ASH.sql_id,\r\n  ASH.sql_opname,\r\n  ASH.sql_exec_id,\r\n  ASH.module\r\nHAVING MAX(temp_space_allocated) &gt; 10000\r\norder by 9 desc\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Using ASH to Monitor Temporary Tablespace Usage To use ASH (Active Session History) to monitor temporary tablespace usage use SQL similar to the query below. Detect the space used by queries. SELECT ASH.inst_id, ASH.user_id, ASH.session_id sid, ASH.session_serial# serial#, ASH.sql_id, ASH.sql_exec_id, ASH.sql_opname, ASH.module, MIN(sample_time) sql_start_time, MAX(sample_time) sql_end_time, ((CAST(MAX(sample_time) AS DATE)) &#8211; (CAST(MIN(sample_time) AS DATE))) * (3600*24)&hellip;&nbsp;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","pgc_sgb_lightbox_settings":"","footnotes":""},"categories":[28,1],"tags":[],"class_list":["post-1425","post","type-post","status-publish","format-standard","hentry","category-oracle-technical","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Oracle - Using ASH to Monitor Temporary Tablespace Usage - dbafox<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbafox.com\/?p=1425\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle - Using ASH to Monitor Temporary Tablespace Usage - dbafox\" \/>\n<meta property=\"og:description\" content=\"Using ASH to Monitor Temporary Tablespace Usage To use ASH (Active Session History) to monitor temporary tablespace usage use SQL similar to the query below. Detect the space used by queries. SELECT ASH.inst_id, ASH.user_id, ASH.session_id sid, ASH.session_serial# serial#, ASH.sql_id, ASH.sql_exec_id, ASH.sql_opname, ASH.module, MIN(sample_time) sql_start_time, MAX(sample_time) sql_end_time, ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24)&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbafox.com\/?p=1425\" \/>\n<meta property=\"og:site_name\" content=\"dbafox\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-12T09:34:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-04-12T09:35:08+00:00\" \/>\n<meta name=\"author\" content=\"Ray Fox\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ray Fox\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1425#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1425\"},\"author\":{\"name\":\"Ray Fox\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"headline\":\"Oracle &#8211; Using ASH to Monitor Temporary Tablespace Usage\",\"datePublished\":\"2019-04-12T09:34:45+00:00\",\"dateModified\":\"2019-04-12T09:35:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1425\"},\"wordCount\":196,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"articleSection\":[\"Oracle Technical\",\"Uncategorized\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/dbafox.com\\\/?p=1425#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1425\",\"url\":\"https:\\\/\\\/dbafox.com\\\/?p=1425\",\"name\":\"Oracle - Using ASH to Monitor Temporary Tablespace Usage - dbafox\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#website\"},\"datePublished\":\"2019-04-12T09:34:45+00:00\",\"dateModified\":\"2019-04-12T09:35:08+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1425#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/dbafox.com\\\/?p=1425\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1425#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/dbafox.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle &#8211; Using ASH to Monitor Temporary Tablespace Usage\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/#website\",\"url\":\"https:\\\/\\\/dbafox.com\\\/\",\"name\":\"dbafox\",\"description\":\"DBA Technical Information\",\"publisher\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/dbafox.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\",\"name\":\"Ray Fox\",\"logo\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/image\\\/\"},\"sameAs\":[\"http:\\\/\\\/www.dbafox.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Oracle - Using ASH to Monitor Temporary Tablespace Usage - dbafox","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbafox.com\/?p=1425","og_locale":"en_GB","og_type":"article","og_title":"Oracle - Using ASH to Monitor Temporary Tablespace Usage - dbafox","og_description":"Using ASH to Monitor Temporary Tablespace Usage To use ASH (Active Session History) to monitor temporary tablespace usage use SQL similar to the query below. Detect the space used by queries. SELECT ASH.inst_id, ASH.user_id, ASH.session_id sid, ASH.session_serial# serial#, ASH.sql_id, ASH.sql_exec_id, ASH.sql_opname, ASH.module, MIN(sample_time) sql_start_time, MAX(sample_time) sql_end_time, ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24)&hellip;&nbsp;","og_url":"https:\/\/dbafox.com\/?p=1425","og_site_name":"dbafox","article_published_time":"2019-04-12T09:34:45+00:00","article_modified_time":"2019-04-12T09:35:08+00:00","author":"Ray Fox","twitter_misc":{"Written by":"Ray Fox","Estimated reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbafox.com\/?p=1425#article","isPartOf":{"@id":"https:\/\/dbafox.com\/?p=1425"},"author":{"name":"Ray Fox","@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"headline":"Oracle &#8211; Using ASH to Monitor Temporary Tablespace Usage","datePublished":"2019-04-12T09:34:45+00:00","dateModified":"2019-04-12T09:35:08+00:00","mainEntityOfPage":{"@id":"https:\/\/dbafox.com\/?p=1425"},"wordCount":196,"commentCount":0,"publisher":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"articleSection":["Oracle Technical","Uncategorized"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbafox.com\/?p=1425#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbafox.com\/?p=1425","url":"https:\/\/dbafox.com\/?p=1425","name":"Oracle - Using ASH to Monitor Temporary Tablespace Usage - dbafox","isPartOf":{"@id":"https:\/\/dbafox.com\/#website"},"datePublished":"2019-04-12T09:34:45+00:00","dateModified":"2019-04-12T09:35:08+00:00","breadcrumb":{"@id":"https:\/\/dbafox.com\/?p=1425#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbafox.com\/?p=1425"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbafox.com\/?p=1425#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbafox.com\/"},{"@type":"ListItem","position":2,"name":"Oracle &#8211; Using ASH to Monitor Temporary Tablespace Usage"}]},{"@type":"WebSite","@id":"https:\/\/dbafox.com\/#website","url":"https:\/\/dbafox.com\/","name":"dbafox","description":"DBA Technical Information","publisher":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbafox.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb","name":"Ray Fox","logo":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/www.dbafox.com"]}]}},"_links":{"self":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/1425","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1425"}],"version-history":[{"count":2,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/1425\/revisions"}],"predecessor-version":[{"id":1427,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/1425\/revisions\/1427"}],"wp:attachment":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1425"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1425"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1425"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}