{"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_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-17","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\/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}]}}