{"id":8,"date":"2012-11-07T16:55:00","date_gmt":"2012-11-07T16:55:00","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=8"},"modified":"2026-02-07T15:02:09","modified_gmt":"2026-02-07T21:02:09","slug":"sysaux-tablespace-growth-review-and-fix","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2012\/8\/","title":{"rendered":"SYSAUX Tablespace Growth &#8211; Review and Fix"},"content":{"rendered":"<div dir=\"ltr\">Received an alert from one of the databases that SYSAUX tablespace is nearing its allocated 24GB space. 24GB is too much for SYSAUX, especially on a development database&#8230; so need to make sure the contents are reviewed before adding more space&#8230;<\/div>\n<div dir=\"ltr\"><\/div>\n<div dir=\"ltr\">\n<p><span style=\"line-height: 1.714285714; font-size: 1rem;\">Oracle10g+ versions have a view <\/span><a style=\"line-height: 1.714285714; font-size: 1rem;\" href=\"http:\/\/docs.oracle.com\/cd\/E14072_01\/server.112\/e10820\/dynviews_3086.htm\" target=\"_blank\">V$SYSAUX_OCCUPANTS<\/a><span style=\"line-height: 1.714285714; font-size: 1rem;\">, showing what components are in SYSAUX tablespace and how much space is used. It also shows the procedure to use if you decide to move this component from SYSAUX to another tablespace.<\/span><\/p>\n<pre class=\"lang:default decode:true \">SQL&gt; col OCCUPANT_NAME format a20\n\nSQL&gt; select occupant_name, space_usage_kbytes\n\u00a0 \u00a0 \u00a0from v$sysaux_occupants\n\u00a0 \u00a0 \u00a0where space_usage_kbytes &gt; 10240\n\u00a0 \u00a0 \u00a0order by space_usage_kbytes desc;\n\nOCCUPANT_NAME \u00a0 \u00a0 \u00a0 \u00a0SPACE_USAGE_KBYTES\n-------------------- ------------------\nSM\/AWR \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a02875776\nLOGMNR \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 573184\nSM\/ADVISOR \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 448384\nXDB \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0177216\nSDO \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 79936\nSTREAMS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 76928\nAO \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a049536\nXSOQHIST \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a049536\nXSAMD \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 31872\nSM\/OTHER \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a017984\nTEXT \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a014720\n\n11 rows selected.\n\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n<p>So, it really does not add up anywhere near 24GB, the largest component is AWR and is only occupying less than 3GB&#8230; What else is in SYSAUX, that does no belong to the standard Oracle components?<\/p>\n<pre class=\"lang:default decode:true \">SQL&gt; col SEGMENT_NAME format a25\nSQL&gt; col segment_type format a10\nSQL&gt; col bytes format \"999,999,999,999\"\nSQL&gt; select segment_name, bytes\u00a0\n\u00a0 \u00a0 \u00a0from dba_segments\u00a0\n\u00a0 \u00a0 \u00a0where tablespace_name = 'SYSAUX'\n\u00a0 \u00a0 \u00a0and bytes &gt; 1048576000 order by bytes;\n\nSEGMENT_NAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 BYTES\n------------------------- ----------------\nSYS_LOB0002516712C00008$$ \u00a0 20,408,434,688\n\n1 row selected.\n\nSQL&gt;<\/pre>\n<p>&nbsp;<\/p>\n<div>It&#8217;s a LOB segment, let us find out which table&#8230;<\/div>\n<div><\/div>\n<div>\n<div>\n<pre class=\"lang:default decode:true \">SQL&gt; select table_name, column_name\n\u00a0 2 \u00a0from dba_lobs\n\u00a0 3 \u00a0where segment_name = 'SYS_LOB0002516712C00008$$';\n\nTABLE_NAME\n------------------------------\nCOLUMN_NAME\n---------------------------------------------\nSTREAMS$_APPLY_SPILL_MSGS_PART\nMESSAGE<\/pre>\n<p>&nbsp;<\/p>\n<\/div>\n<\/div>\n<div>Aaaha! We recently enabled Change Data Capture feature on this database, and it is using STREAMS&#8230; Now, need to see what&#8217;s causing it to grow and how to fix&#8230;!<\/div>\n<div><\/div>\n<div>Also, shows that V$SYSAUX_OCCUPANTS do not consider the LOB segments, when calculating space occupied \u00a0\ud83d\ude41<\/div>\n<div><\/div>\n<div><\/div>\n<div><\/div>\n<p>&nbsp;<\/p>\n<div><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Received an alert from one of the databases that SYSAUX tablespace is nearing its allocated 24GB space. 24GB is too much for SYSAUX, especially on a development database&#8230; so need to make sure the contents are reviewed before adding more space&#8230; Oracle10g+ versions have a view V$SYSAUX_OCCUPANTS, showing what components are in SYSAUX tablespace and &#8230; <a title=\"SYSAUX Tablespace Growth &#8211; Review and Fix\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2012\/8\/\" aria-label=\"Read more about SYSAUX Tablespace Growth &#8211; Review and Fix\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[133],"tags":[141],"class_list":["post-8","post","type-post","status-publish","format-standard","hentry","category-oracledb","tag-tablespace"],"acf":[],"jetpack-related-posts":[{"id":2032,"url":"https:\/\/bijoos.com\/oraclenotes\/2017\/2032\/","url_meta":{"origin":8,"position":0},"title":"Oracle Tidbits &#8211; January 2017 #oratidbit","author":"Biju","date":"February 1, 2017","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published on Facebook, Twitter and Google+\u00a0during weekdays\u00a0in January 2017. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well\u2026 Hope you find these helpful to learn something new or to remind you\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2017\/02\/oratidbit.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1781,"url":"https:\/\/bijoos.com\/oraclenotes\/2016\/1781\/","url_meta":{"origin":8,"position":1},"title":"Oracle Tidbits &#8211;  November 2016 #oratidbit","author":"Biju","date":"December 2, 2016","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published at\u00a0https:\/\/www.facebook.com\/oraclenotes\u00a0on weekdays\u00a0in November 2016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well\u2026 Hope you find these helpful to learn something new or to remind you of its existence and\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"Oracle TidBit","src":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2014\/05\/TidBit.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1444,"url":"https:\/\/bijoos.com\/oraclenotes\/2014\/1444\/","url_meta":{"origin":8,"position":2},"title":"Oracle Tidbits &#8211; August 2014","author":"Biju","date":"September 1, 2014","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d published at\u00a0https:\/\/www.facebook.com\/oraclenotes\u00a0on all weekdays\u00a0in August\u00a02014. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well\u2026 Hope you find these helpful to learn something new or to remind you of its existence and use...\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2140,"url":"https:\/\/bijoos.com\/oraclenotes\/2018\/2140\/","url_meta":{"origin":8,"position":3},"title":"Oracle Tidbits &#8211; January 2018 #oratidbit","author":"Biju","date":"February 1, 2018","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published on Facebook, Twitter, and Google+\u00a0during weekdays\u00a0in January 2018. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well\u2026 Hope you find these helpful to learn something new or to remind you\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":21,"url":"https:\/\/bijoos.com\/oraclenotes\/2011\/21\/","url_meta":{"origin":8,"position":4},"title":"Read Only Tables [Orig Published Apr 2000]","author":"Biju","date":"September 26, 2011","format":false,"excerpt":"In Oracle (all versions including 10g) you can make a tablespace read only, but not a single table. To make a table read only, you need to have that table in its own tablespace. If the tablespace has many tables, then you can either make all the tables read only\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":17,"url":"https:\/\/bijoos.com\/oraclenotes\/2011\/17\/","url_meta":{"origin":8,"position":5},"title":"Query &#8211; Tablespace Max Size","author":"Biju","date":"September 27, 2011","format":false,"excerpt":"I sometimes see concerned emails from developers that they are running out of space in tablespace and the DBA need to do something immediately. The DBA will start wondering we have monitoring in place, then how come it did not catch an out of space situation, but a user\/developer did...For\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/8","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=8"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/8\/revisions"}],"predecessor-version":[{"id":2958,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/8\/revisions\/2958"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=8"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=8"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=8"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}