{"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 – Review and Fix"},"content":{"rendered":"
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… so need to make sure the contents are reviewed before adding more space…<\/div>\n
<\/div>\n
\n

Oracle10g+ versions have a view <\/span>V$SYSAUX_OCCUPANTS<\/a>, 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

SQL> col OCCUPANT_NAME format a20\n\nSQL> select occupant_name, space_usage_kbytes\n\u00a0 \u00a0 \u00a0from v$sysaux_occupants\n\u00a0 \u00a0 \u00a0where space_usage_kbytes > 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><\/pre>\n
 <\/p>\n
So, it really does not add up anywhere near 24GB, the largest component is AWR and is only occupying less than 3GB… What else is in SYSAUX, that does no belong to the standard Oracle components?<\/p>\n
SQL> col SEGMENT_NAME format a25\nSQL> col segment_type format a10\nSQL> col bytes format \"999,999,999,999\"\nSQL> select segment_name, bytes\u00a0\n\u00a0 \u00a0 \u00a0from dba_segments\u00a0\n\u00a0 \u00a0 \u00a0where tablespace_name = 'SYSAUX'\n\u00a0 \u00a0 \u00a0and bytes > 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><\/pre>\n
 <\/p>\n
It’s a LOB segment, let us find out which table…<\/div>\n
<\/div>\n
\n
\n
SQL> 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>\n<\/div>\n<\/div>\n
Aaaha! We recently enabled Change Data Capture feature on this database, and it is using STREAMS… Now, need to see what’s causing it to grow and how to fix…!<\/div>\n
<\/div>\n
Also, shows that V$SYSAUX_OCCUPANTS do not consider the LOB segments, when calculating space occupied \u00a0\ud83d\ude41<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
 <\/p>\n
<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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… so need to make sure the contents are reviewed before adding more space… Oracle10g+ versions have a view V$SYSAUX_OCCUPANTS, showing what components are in SYSAUX tablespace and … 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_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[133],"tags":[141],"class_list":["post-8","post","type-post","status-publish","format-standard","hentry","category-oracledb","tag-tablespace"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_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}]}}