{"id":17,"date":"2011-09-27T22:55:00","date_gmt":"2011-09-27T22:55:00","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=17"},"modified":"2026-02-07T15:02:10","modified_gmt":"2026-02-07T21:02:10","slug":"query-tablespace-max-size","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2011\/17\/","title":{"rendered":"Query &#8211; Tablespace Max Size"},"content":{"rendered":"<div dir=\"ltr\" trbidi=\"on\">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&#8230;<\/p>\n<p>For you developer\/user, if you are wondering if your database\/tablespace is running out of space, you may need to check if the tablespace has autoextensible data files&#8230;<\/p>\n<p>Here is a simple query, that could be used to find the current size of the tablespace and how much it can grow using the autoextensible feature of data file.<\/p>\n<p><span>\u00a0 SELECT tablespace_name,<\/span><br \/><span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0ROUND (SUM (bytes) \/ 1048576) curr_size,<\/span><br \/><span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0ROUND (SUM (GREATEST (bytes, DECODE (autoextensible, &#8216;YES&#8217;, maxbytes, 0)))\/ 1048576) \u00a0growable<\/span><br \/><span>\u00a0 \u00a0 FROM dba_data_files<\/span><br \/><span>GROUP BY tablespace_name<\/span><br \/><span>ORDER BY tablespace_name;<\/span><\/p>\n<p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>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&#8230; For you developer\/user, if you &#8230; <a title=\"Query &#8211; Tablespace Max Size\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2011\/17\/\" aria-label=\"Read more about Query &#8211; Tablespace Max Size\">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-17","post","type-post","status-publish","format-standard","hentry","category-oracledb","tag-tablespace"],"acf":[],"jetpack-related-posts":[{"id":12,"url":"https:\/\/bijoos.com\/oraclenotes\/2011\/12\/","url_meta":{"origin":17,"position":0},"title":"Tablespace Free Space and Max Size","author":"Biju","date":"October 27, 2011","format":false,"excerpt":"Earlier, provided a small script to find the maximum growable size of a tablespace... Here is little more comprehensive script that shows more tablespace space info. Current size, growable size based on the autoextensible data files, free space, biggest chunk...All size is in MB. SELECT a.tablespace_name TSNAME,\u00a0 \u00a0 \u00a0 \u00a0\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":8,"url":"https:\/\/bijoos.com\/oraclenotes\/2012\/8\/","url_meta":{"origin":17,"position":1},"title":"SYSAUX Tablespace Growth &#8211; Review and Fix","author":"Biju","date":"November 7, 2012","format":false,"excerpt":"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\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":1720,"url":"https:\/\/bijoos.com\/oraclenotes\/2016\/1720\/","url_meta":{"origin":17,"position":2},"title":"Oracle Tidbits &#8211; May 2016","author":"Biju","date":"June 1, 2016","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published at\u00a0https:\/\/www.facebook.com\/oraclenotes\u00a0on weekdays\u00a0in May\u00a02016. 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\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":17,"position":3},"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":2032,"url":"https:\/\/bijoos.com\/oraclenotes\/2017\/2032\/","url_meta":{"origin":17,"position":4},"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":1534,"url":"https:\/\/bijoos.com\/oraclenotes\/2015\/1534\/","url_meta":{"origin":17,"position":5},"title":"Oracle #DB12c New &#8211; TEMP_UNDO_ENABLED","author":"Biju","date":"June 24, 2015","format":false,"excerpt":"Global temporary table data is stored in the temporary tablespace (where no redo will be generated on the DML operations). When DML operations are performed on global temporary table, for read consistency undo must be generated. Such undo records are written to undo tablespace, and thus redo generated on the\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"ManyThingsOracle-pic1","src":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2015\/06\/ManyThingsOracle-pic1-300x191.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/17","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=17"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/17\/revisions"}],"predecessor-version":[{"id":2960,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/17\/revisions\/2960"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=17"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=17"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=17"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}