{"id":7239,"date":"2021-05-28T20:24:28","date_gmt":"2021-05-28T20:24:28","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/manage-the-sysaux-tablespace\/"},"modified":"2026-11-03T19:28:17","modified_gmt":"2026-11-03T19:28:17","slug":"manage-the-sysaux-tablespace","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/manage-the-sysaux-tablespace\/","title":{"rendered":"Manage the SYSAUX tablespace"},"content":{"rendered":"<p><strong>Introduction<\/strong><\/p>\n<p>Oracle 10g introduced a new mandatory tablespace called SYSAUX with mandatory attributes like <strong>PERMANENT<\/strong>, <strong>READ WRITE<\/strong>,&nbsp;<strong>EXTENT MANAGEMENT LOCAL<\/strong>, and <strong>SEGMENT SPACE MANAGEMENT AUTO<\/strong>. This post explores how to manage this tablespace as it grows.<\/p>\n<p>You can use the SYSAUX tablespace to perform the following tasks:<\/p>\n<ul>\n<li>Avoid SYSTEM tablespace fragmentation caused by install and deinstall options.<\/li>\n<li>Avoid risking &nbsp;SYSTEM tablespace corruption and out-of-space situations.<\/li>\n<li>Reduce maintenance for the database administrator.<\/li>\n<li>Store and reduce tablespaces for all auxiliary database metadata related to<\/li>\n<li>Oracle options and features, such as the following items:<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26328-body-Picture1_48.png\" alt=\"sysaux Pic 1\" \/><\/p>\n<p><strong>SYSAUX occupants&nbsp;<\/strong><\/p>\n<p>There are 26 SYSAUX occupants that you can query, as shown in the following example:<\/p>\n<p><em>&nbsp; &nbsp; SQL&gt; select OCCUPANT_NAME,OCCUPANT_DESC<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from V$SYSAUX_OCCUPANTS<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;order by SPACE_USAGE_KBYTES desc<\/em><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26328-body-Picture2_38.png\" alt=\"sysaux pic 2\" \/><\/p>\n<p>Later versions of Oracle Database added even more features, as shown in the following image:&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26328-body-Picture3_39.png\" alt=\"sysaux pic 3\" \/><\/p>\n<p>The following image shows the components added or deprecated in each Oracle Database version:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26328-body-Picture4a.png\" alt=\"sysaux pic 4 a \" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26328-body-Picture4b.png\" alt=\"sysaux pic 4b\" \/><\/p>\n<p><strong>&nbsp;Proactive SYSAUX tablespace management<\/strong><\/p>\n<p>\nTo proactively monitor and manage SYSAUX tablespace proactively, perform the following actions:<\/p>\n<p>1. Set the SYSAUX tablespace to <strong>AUTOEXTEND off<\/strong>.<br \/>\n2. Confirm the <strong>STATISTICS_LEVEL<\/strong>&nbsp;value.<\/p>\n<p>&nbsp; &nbsp; &nbsp; &nbsp;&#8211; The <strong>ALL<\/strong> value is sometimes resource-intensive.<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp;&#8211; The <strong>Basic<\/strong> and <strong>Typical<\/strong>&nbsp;values tend to consume fewer resources.<\/p>\n<p>3. Check how you use advisors, baselines, and SQL tuning sets. Advisors need to retain information in snapshots even if you plan to drop the snapshot range.<\/p>\n<p>4. Run a query to determine which sysaux_occupant consumes the most space in the&nbsp;SYSAUX tablespace.<\/p>\n<p><strong>Remedial SYSAUX tablespace management<\/strong><\/p>\n<p>The SYSAUX tablespace grows for the following reasons:<\/p>\n<ul>\n<li>You set an excessive retention period.<\/li>\n<li>The segment advisor grows too large.<\/li>\n<li>The active session history (ASH) grows too large.<\/li>\n<\/ul>\n<p>The following sections offer some remedial actions<\/p>\n<p><strong>Check the AWR retention period<\/strong><\/p>\n<p>To manage this, check the retention period of the Automatic Workload Repository (AWR) snapshot. The AWR collects the performance statistics for problem detection and performance tuning and stores the details in memory and database tables. The system deletes stored data based on the retention period. If the retention period is too high, this data consumes more space in the SYSAUX tablespace. So, you should set an appropriate retention period. Check the retention period by using the following query:<\/p>\n<p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<em>&nbsp;SQL&gt; SELECT retention FROM dba_hist_wr_control;<\/em><\/p>\n<p>You can alter the retention manually by using the<br \/>\n<em>DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS<\/em>&nbsp;procedure. &nbsp;The following example sets the retention period to 5760 minutes (or four days:<br \/>\n4 days \\* 24 hours per day \\* 60 minutes per hour = 5760 minutes):<\/p>\n<p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <em>&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;SQL&gt; execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT(RETENTION=&gt;5760);<\/em><\/p>\n<p><strong>Check the largest object in the SYSAUX tablespace<\/strong><\/p>\n<p>Run the following query to identify the largest occupant in the tablespace:<\/p>\n<p><em>SQL&gt; select OCCUPANT_NAME, SCHEMA_NAME, MOVE_PROCEDURE, SPACE_USAGE_KBYTES<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from v$sysaux_occupants;<\/em><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26328-body-Picture5_29.png\" alt=\"sysaux pic 5\" \/><\/p>\n<p>You use the <strong>move<\/strong> procedure command to move the occupant to another tablespace unless the <strong>move<\/strong>&nbsp;procedure is null.&nbsp;<\/p>\n<p>The following example moves WKSYS occupant in XYZ tablespace:<\/p>\n<p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<em>&nbsp; &nbsp;SQL&gt; execute WKSYS.MOVE_WK(\u2018XYZ\u2019);<\/em><\/p>\n<p><strong>Check the Active Session History (ASH)<\/strong><\/p>\n<p>See if the ASH consumes more space in AWR data by using the following script:<\/p>\n<p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<em> &nbsp; &nbsp;SQL&gt; @$ORACLE_HOME\/rdbms\/admin\/awrinfo.sql<\/em><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26328-body-Picture6_19.png\" alt=\"sysaux pic 6\" \/><\/p>\n<p>\nASH usage is acceptable at 1.1 percent. If it is high, delete orphaned ASH rows. Check for orphaned rows by using the following query:<\/p>\n<p><em>SQL&gt; SELECT COUNT(1) Orphaned_ASH_Rows FROM wrh$_active_session_history a<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE NOT EXISTS (SELECT 1 FROM wrm$_snapshot WHERE snap_id &nbsp;= a.snap_id<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND dbid= a.dbid AND instance_number = a.instance_number);<\/em><br \/>\n&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26328-body-Picture7_19.png\" alt=\"sysaux pic 7\" \/><\/p>\n<p>Because the value is larger than zero, delete. View the orphans by using the following query:<\/p>\n<p>&nbsp; <em>&nbsp; SQL&gt; DELETE FROM wrh$_active_session_history a WHERE NOT EXISTS<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(SELECT 1 FROM wrm$_snapshot WHERE snap_id = a.snap_id<br \/>\n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND dbid = a.dbid AND instance_number = a.instance_number);&nbsp;<\/em><\/p>\n<p>Then, run the following query to shrink the <strong>WRH$_ACTIVE_SESSION_HISTORY<\/strong><br \/>\ntable to reclaim the freed space:<\/p>\n<p>&nbsp; &nbsp;<em> &nbsp;SQL&gt; alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;<\/em><\/p>\n<p><strong>Conclusion<\/strong><\/p>\n<p>This post introduces the SYSAUX tablespace. It also provides suggestions on monitoring and managing space growth in the SYSAUX tablespace default occupants and identifying any non-default objects wrongly stored in the tablespace.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Manage the SYSAUX tablespace<\/p>\n","protected":false},"author":2,"featured_media":7241,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26328","content_type":"blog","created_by":"10130","authored_on":"2023-11-30T21:24:27+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Nirbhay Kumar Singh, Oracle Database Administrator, Rackspace Technology","seo_description":"Discover the functionalities of Oracle's mandatory SYSAUX tablespace introduced in Oracle 10g and learn how to manage its expansion.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26328-insights_image-oracle-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2021-05-28T20:24:28","rxt_solutions":"Databases, Data Management","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Managing the SYSAUX Tablespace | Rackspace Technology","field_topics":"","field_slug":"manage-the-sysaux-tablespace","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7239","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7239","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7239"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7239\/revisions"}],"predecessor-version":[{"id":9215,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7239\/revisions\/9215"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7241"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}