{"id":2279,"date":"2025-01-19T10:18:04","date_gmt":"2025-01-19T10:18:04","guid":{"rendered":"https:\/\/w3buddy.com\/?p=2279"},"modified":"2026-01-15T13:19:10","modified_gmt":"2026-01-15T07:49:10","slug":"what-is-undo_retention-how-it-works-and-why-it-matter","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/what-is-undo_retention-how-it-works-and-why-it-matter\/","title":{"rendered":"What is UNDO_RETENTION: How It Works and Why It Matter"},"content":{"rendered":"\n

The UNDO_RETENTION<\/strong> parameter in Oracle is an important setting that determines how long undo data (old versions of modified data) should be retained before it\u2019s overwritten. However, it\u2019s often misunderstood or seen as ineffective in solving issues like ORA-01555<\/strong>. This post explains how UNDO_RETENTION<\/strong> works, when it matters, and what you can do to manage undo space.<\/p>\n\n\n\n

What is UNDO_RETENTION?<\/h3>\n\n\n\n

UNDO_RETENTION<\/strong> specifies how long Oracle should retain undo data before it can be overwritten. It helps ensure that undo information stays available for queries and transactions that need it. However, it only works effectively under certain conditions, and it\u2019s important to understand how it interacts with your database setup.<\/p>\n\n\n\n

Key Points to Know:<\/h3>\n\n\n\n
    \n
  1. Fixed-Size Undo Tablespace<\/strong>:\n
      \n
    • If your undo tablespace has a fixed size, the UNDO_RETENTION<\/strong> setting is ignored.<\/li>\n\n\n\n
    • In this case, Oracle automatically manages the undo retention period based on system activity and the size of the undo tablespace.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
    • Autoextend Undo Tablespace<\/strong>:\n
        \n
      • If your undo tablespace is set to autoextend, Oracle will try to respect the UNDO_RETENTION<\/strong> setting.<\/li>\n\n\n\n
      • When the tablespace runs low on space, it will autoextend instead of deleting older undo data.<\/li>\n\n\n\n
      • However, if the maximum size of the undo tablespace is reached, Oracle may begin overwriting undo data that hasn\u2019t expired yet.<\/li>\n\n\n\n
      • The UNDOTBS1<\/strong> tablespace created by the Database Configuration Assistant (DBCA) is typically autoextending by default.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n

        What to Do If Your Undo Space Grows Too Much?<\/h3>\n\n\n\n

        If you notice that the undo tablespace is growing more than expected, and simply adjusting the UNDO_RETENTION<\/strong> setting doesn\u2019t seem to help, here\u2019s a quick solution you can try.<\/p>\n\n\n\n

          \n
        • Temporary Fix<\/strong>: You can reduce the UNDO_RETENTION<\/strong> value temporarily in memory. This will cause most of the undo segments to expire, freeing up space. Once expired, Oracle will reclaim the space and stop the undo tablespace from growing.<\/li>\n<\/ul>\n\n\n\n

          How to Check Your Undo Usage?<\/h3>\n\n\n\n

          To check the undo retention period and other related details, you can use Oracle\u2019s built-in functions. Here are some examples:<\/p>\n\n\n\n

          -- Get the best possible undo retention over the last 7 days\nSQL> select dbms_undo_adv.best_possible_retention(sysdate-7, sysdate) UNDO_RETENTION from dual;\n\nUNDO_RETENTION\n--------------\n       1583948<\/code><\/pre>\n\n\n\n
          1583948 seconds<\/strong> (~18.3 days).<\/p>\n\n\n\n
          -- Get the longest running query\u2019s undo retention over the last 7 days\nSQL> select dbms_undo_adv.longest_query(sysdate-7, sysdate) UNDO_RETENTION from dual;\n\nUNDO_RETENTION\n--------------\n          7182<\/code><\/pre>\n\n\n\n
          7182 seconds<\/strong> (~2 hours).<\/p>\n\n\n\n
          -- Get the required undo retention over the last 7 days\nSQL> select dbms_undo_adv.required_retention(sysdate-7, sysdate) UNDO_RETENTION from dual;\n\nUNDO_RETENTION\n--------------\n          7182<\/code><\/pre>\n\n\n\n
          7182 seconds<\/strong> (~2 hours).<\/p>\n\n\n\n
          -- Get the undo retention needed for RBU migration over the last 7 days\nSQL> select dbms_undo_adv.rbu_migration(sysdate-7, sysdate) UNDO_RETENTION from dual;\n\nUNDO_RETENTION\n--------------\n          1600<\/code><\/pre>\n\n\n\n
          1600 seconds<\/strong> (~26.6 minutes).<\/p>\n\n\n\n
          Each of these queries helps you determine different aspects of UNDO_RETENTION<\/strong> for your system. These values are all in seconds<\/strong>, indicating how long undo information needs to be retained.<\/p>\n\n\n\n
          Understanding the UNDO_RETENTION Guarantee<\/h3>\n\n\n\n
          The effectiveness of UNDO_RETENTION<\/strong> depends on the type of undo tablespace management you are using. Here’s a simple matrix that shows how UNDO_RETENTION<\/strong> works under different conditions:<\/p>\n\n\n\n
          Space Type<\/th>No Guarantee<\/th>With Guarantee<\/th><\/tr><\/thead>
          Fixed-Size<\/td>Ignored<\/td>Guaranteed until no free space is left<\/td><\/tr>
          Autoextend<\/td>Honored<\/td>Guaranteed until MAXSIZE is reached<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
            \n
          • No Guarantee<\/strong>: For a fixed-size undo tablespace, UNDO_RETENTION<\/strong> is ignored. Oracle does its best to retain undo data but may overwrite it when space runs out.<\/li>\n\n\n\n
          • With Guarantee<\/strong>: For an autoextending undo tablespace, UNDO_RETENTION<\/strong> is respected until the maximum size is reached. Once the limit is hit, Oracle may start overwriting unexpired undo data.<\/li>\n<\/ul>\n\n\n\n
            Conclusion<\/h3>\n\n\n\n
            In summary, UNDO_RETENTION<\/strong> is an essential parameter for managing undo data in Oracle. It can help ensure undo information is available when needed, but its effectiveness depends on the undo tablespace configuration (fixed-size vs. autoextend). If you notice the undo tablespace growing uncontrollably, adjusting the UNDO_RETENTION<\/strong> temporarily can help reclaim space. Regular monitoring and adjustments are key to ensuring optimal database performance.<\/p>\n\n\n\n
            <\/p>\n","protected":false},"excerpt":{"rendered":"
            The UNDO_RETENTION parameter in Oracle is an important setting that determines how long undo data (old versions of modified data) should be retained before it\u2019s overwritten. However, it\u2019s often misunderstood or seen as ineffective in solving issues like ORA-01555. This post explains how UNDO_RETENTION works, when it matters, and what you can do to manage […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-2279","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2279","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=2279"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2279\/revisions"}],"predecessor-version":[{"id":2280,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2279\/revisions\/2280"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=2279"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=2279"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=2279"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}