{"id":1176,"date":"2026-07-16T10:36:28","date_gmt":"2026-07-16T08:36:28","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=1176"},"modified":"2026-07-16T10:36:28","modified_gmt":"2026-07-16T08:36:28","slug":"make-oracle-tablespace-management-easier-make-sure-to-have-autoextend-on-and-maxsize-unlimited","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2026\/07\/16\/make-oracle-tablespace-management-easier-make-sure-to-have-autoextend-on-and-maxsize-unlimited\/","title":{"rendered":"Make Oracle tablespace management easier &#8211; make sure to have autoextend on and maxsize unlimited"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">On several customers where databases exists since many years, it is common to find datafile and tablespace management to be inconsistent, mainly due to historical reasons and the DBA preferences at the time.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It is common to see a mix of these patterns in a typical 8192 block size database using smallsize tablespaces:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Datafiles of fixed sized, and DBA increases every few months (or when an alert comes) the size by some GB<\/li>\n\n\n\n<li>Datafiles near maxsize, for instance 30GB or 32000M, and the DBA adds new datafiles this new size with autoextend off<\/li>\n\n\n\n<li>Datafiles near maxsize, for instance 30GB or 32000M, and the DBA adds new datafiles with small size but autoextend on<\/li>\n\n\n\n<li>Datafiles with real maxsize and autoextend on<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">I try always to educate that we should monitor mainly the disk space, not tablespace size. We can monitor the growth, but I don&#8217;t want to wake anyone at night because of the tablespace is getting full.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Today I&#8217;ve just fixed on the customer databases all the tablespaces, setting all datafiles with autoextend on and maxsize unlimited. It is important to make sure there is a disk space monitor behind it.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To check which tablespaces are impacted by not having the real datafile maximum size, this query can help:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nselect tablespace_name, round(sum(maxbytes)\/1024\/1024\/1024) current_max_gb, round(sum((power(2,22)-2)-maxblocks)*sum(maxbytes)\/sum(maxblocks)\/1024\/1024\/1024) to_possible_max_gb \nfrom dba_data_files \nwhere maxblocks!=(power(2,22)-2) \n    or autoextensible!=&#039;YES&#039; \ngroup by tablespace_name \norder by 1;\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Here the code to fix this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nbegin \n  for df in (select file_id from dba_data_files where maxblocks!=(power(2,22)-2) or autoextensible!=&#039;YES&#039;) loop\n    execute immediate &#039;alter database datafile &#039;||df.file_id||&#039; autoextend on maxsize unlimited&#039;;\n  end loop;\nend;\n\/\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">For smallsize tablespaces, the maximum number of blocks is<em> power(2,22)-2<\/em>. On internet, it is sometimes written <em>power(2,22)-1<\/em>, however, when looking at <code>dba_data_files.maxblocks <\/code>for a unlimited size datafile, the number corresponds to <em>power(2,22)-2<\/em>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ah, for creating and adding datafiles to tablespaces, this is what I use, so it is automatically with autoextend on and unlimited datafile size &#8211; both are default:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nCREATE TABLESPACE tbs_name;\nALTER TABLESPACE tbs_name ADD DATAFILE;\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">It expects the usage of OMF &#8211; <code>db_create_file_dest <\/code>parameter is set.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Even better is when OEM Corrective Action is configured correctly, as it takes care of adding datafiles automatically.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>On several customers where databases exists since many years, it is common to find datafile and tablespace management to be inconsistent, mainly due to historical reasons and the DBA preferences at the time. It is common to see a mix of these patterns in a typical 8192 block size database using smallsize tablespaces: I try [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-1176","post","type-post","status-publish","format-standard","category-oracle","czr-hentry"],"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1176","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=1176"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1176\/revisions"}],"predecessor-version":[{"id":1177,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1176\/revisions\/1177"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=1176"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=1176"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=1176"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}