{"id":13170,"date":"2019-10-14T06:55:05","date_gmt":"2019-10-14T06:55:05","guid":{"rendered":"https:\/\/dbtut.com\/?p=13170"},"modified":"2019-10-14T06:55:06","modified_gmt":"2019-10-14T06:55:06","slug":"find-undo-usage-by-sessions","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/","title":{"rendered":"Find UNDO Usage by Sessions"},"content":{"rendered":"

You can find the undo usage of a session with the following query. Historical information is also available.<\/p>\n

You can find the undo usage of all sessions or active sessions by changing the conditions in the query.<\/p>\n

  SELECT r.NAME \"Undo Segment Name\",\n         dba_seg.size_mb,\n         DECODE (TRUNC (SYSDATE - LOGON_TIME),\n                 0, NULL,\n                 TRUNC (SYSDATE - LOGON_TIME) || ' Days' || ' + ')\n         || TO_CHAR (\n               TO_DATE (TRUNC (MOD (SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'),\n               'HH24:MI:SS')\n            LOGON,\n         v$session.SID,\n         v$session.SERIAL#,\n         p.SPID,\n         v$session.process,\n         v$session.USERNAME,\n         v$session.STATUS,\n         v$session.OSUSER,\n         v$session.MACHINE,\n         v$session.PROGRAM,\n         v$session.module,\n         action\n    FROM v$lock l,\n         v$process p,\n         v$rollname r,\n         v$session,\n         (  SELECT segment_name, ROUND (bytes \/ (1024 * 1024), 2) size_mb\n              FROM dba_segments\n             WHERE segment_type = 'TYPE2 UNDO'\n          ORDER BY bytes DESC) dba_seg\n   WHERE     l.SID = p.pid(+)\n         AND v$session.SID = l.SID\n         AND TRUNC (l.id1(+) \/ 65536) = r.usn\n         AND l.TYPE(+) = 'TX'\n         AND l.lmode(+) = 6\n         AND r.NAME = dba_seg.segment_name\n--AND v$session.username = 'ADURUOZ'\n--AND status = 'ACTIVE'\nORDER BY size_mb DESC;<\/pre>\n
The following query also provides summary information.<\/p>\n
  SELECT u.tablespace_name tablespace,\n         s.username,\n         u.status,\n         SUM (u.bytes) \/ 1024 \/ 1024 sum_in_mb,\n         COUNT (u.segment_name) seg_cnts\n    FROM dba_undo_extents u\n         LEFT JOIN v$transaction t\n            ON u.segment_name = '_SYSSMU' || t.xidusn || '\n\n\n\n         LEFT JOIN v$session s\n            ON t.addr = s.taddr\nGROUP BY u.tablespace_name, s.username, u.status\nORDER BY 1, 2, 3;<\/pre>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
You can find the undo usage of a session with the following query. Historical information is also available. You can find the undo usage of all sessions or active sessions by changing the conditions in the query. SELECT r.NAME “Undo Segment Name”, dba_seg.size_mb, DECODE (TRUNC (SYSDATE – LOGON_TIME), 0, NULL, TRUNC (SYSDATE – LOGON_TIME) || …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":13171,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[4],"tags":[4871,4881,4877,4882,4880,4876,4875,4879,4870,4878,4874,4872,4873],"class_list":["post-13170","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle","tag-find-sessions-that-use-the-most-undo","tag-find-who-is-using-lot-of-undo","tag-how-to-check-undo-tablespace-usage","tag-how-to-find-the-cause-of-sudden-undo-tablespace-increase","tag-how-to-find-the-session-using-lot-of-undo","tag-query-to-find-which-sessions-consume-undo","tag-query-to-find-which-transactions-sessions-consume-undo","tag-script-to-find-who-is-using-undo-space-in-oracle","tag-undo-usage-by-sessions","tag-who-is-using-more-undo","tag-who-is-using-undo","tag-who-is-using-your-undo-space","tag-who-use-undo"],"aioseo_notices":[],"yoast_head":"\nFind UNDO Usage by Sessions - Database Tutorials<\/title>\n<meta name=\"description\" content=\"Find UNDO Usage by Sessions\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Find UNDO Usage by Sessions - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Find UNDO Usage by Sessions\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-10-14T06:55:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-10-14T06:55:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/10\/Ads\u0131z-3.png\" \/>\n\t<meta property=\"og:image:width\" content=\"607\" \/>\n\t<meta property=\"og:image:height\" content=\"396\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. 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:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Find UNDO Usage by Sessions\",\"datePublished\":\"2019-10-14T06:55:05+00:00\",\"dateModified\":\"2019-10-14T06:55:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/\"},\"wordCount\":52,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/10\/Ads\u0131z-3.png\",\"keywords\":[\"find sessions that use the most undo\",\"find who is using lot of undo\",\"How to check UNDO tablespace usage\",\"How to find the cause of sudden Undo TableSpace increase\",\"How to find the session using lot of undo\",\"Query To find which sessions consume undo\",\"Query To find which transactions\/sessions consume undo\",\"Script to find who is using UNDO space in Oracle\",\"undo usage by sessions\",\"who is using more undo\",\"Who is using UNDO\",\"Who is using your UNDO space\",\"Who use undo\"],\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/\",\"name\":\"Find UNDO Usage by Sessions - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/10\/Ads\u0131z-3.png\",\"datePublished\":\"2019-10-14T06:55:05+00:00\",\"dateModified\":\"2019-10-14T06:55:06+00:00\",\"description\":\"Find UNDO Usage by Sessions\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/10\/Ads\u0131z-3.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/10\/Ads\u0131z-3.png\",\"width\":607,\"height\":396},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Find UNDO Usage by Sessions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Find UNDO Usage by Sessions - Database Tutorials","description":"Find UNDO Usage by Sessions","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:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/","og_locale":"en_US","og_type":"article","og_title":"Find UNDO Usage by Sessions - Database Tutorials","og_description":"Find UNDO Usage by Sessions","og_url":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/","og_site_name":"Database Tutorials","article_published_time":"2019-10-14T06:55:05+00:00","article_modified_time":"2019-10-14T06:55:06+00:00","og_image":[{"width":607,"height":396,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/10\/Ads\u0131z-3.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Find UNDO Usage by Sessions","datePublished":"2019-10-14T06:55:05+00:00","dateModified":"2019-10-14T06:55:06+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/"},"wordCount":52,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/10\/Ads\u0131z-3.png","keywords":["find sessions that use the most undo","find who is using lot of undo","How to check UNDO tablespace usage","How to find the cause of sudden Undo TableSpace increase","How to find the session using lot of undo","Query To find which sessions consume undo","Query To find which transactions\/sessions consume undo","Script to find who is using UNDO space in Oracle","undo usage by sessions","who is using more undo","Who is using UNDO","Who is using your UNDO space","Who use undo"],"articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/","url":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/","name":"Find UNDO Usage by Sessions - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/10\/Ads\u0131z-3.png","datePublished":"2019-10-14T06:55:05+00:00","dateModified":"2019-10-14T06:55:06+00:00","description":"Find UNDO Usage by Sessions","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/10\/Ads\u0131z-3.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/10\/Ads\u0131z-3.png","width":607,"height":396},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/10\/14\/find-undo-usage-by-sessions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Find UNDO Usage by Sessions"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/13170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=13170"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/13170\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/13171"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=13170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=13170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=13170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}