{"id":12,"date":"2011-10-27T20:31:00","date_gmt":"2011-10-27T20:31:00","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=12"},"modified":"2026-02-07T15:02:10","modified_gmt":"2026-02-07T21:02:10","slug":"tablespace-free-space-and-max-size","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2011\/12\/","title":{"rendered":"Tablespace Free Space and Max Size"},"content":{"rendered":"<div dir=\"ltr\">Earlier, provided a small script to find the maximum growable size of a tablespace&#8230; 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&#8230;All size is in MB.<\/p>\n<pre class=\"lang:default decode:true  crayon-selected\">\nSELECT a.tablespace_name TSNAME,\u00a0\n\u00a0 \u00a0 \u00a0 \u00a0 ROUND(SUM(a.growable)\/1048576) growable,\u00a0\n\u00a0 \u00a0 \u00a0 \u00a0 ROUND(SUM(a.tots)\/1048576) Tot_Size,\n\u00a0 \u00a0 \u00a0 \u00a0 ROUND(SUM(a.sumb)\/1048576) Tot_Free,\n\u00a0 \u00a0 \u00a0 \u00a0 ROUND(SUM(a.sumb)*100\/sum(a.tots)) Pct_Free,\n\u00a0 \u00a0 \u00a0 \u00a0 ROUND(SUM(a.largest)\/1048576) Large_Ext\nFROM \u00a0 (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0MAX(bytes) largest, 0 growable\n\u00a0 \u00a0 \u00a0 \u00a0 FROM \u00a0 dba_free_space a\n\u00a0 \u00a0 \u00a0 \u00a0 GROUP BY tablespace_name\n\u00a0 \u00a0 \u00a0 \u00a0 UNION\n\u00a0 \u00a0 \u00a0 \u00a0 SELECT tablespace_name, SUM(bytes) tots, 0, 0, \u00a0SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable\n\u00a0 \u00a0 \u00a0 \u00a0 FROM \u00a0 dba_data_files\n\u00a0 \u00a0 \u00a0 \u00a0 GROUP BY tablespace_name\n\u00a0 \u00a0 \u00a0 \u00a0 UNION\n\u00a0 \u00a0 \u00a0 \u00a0 SELECT tablespace_name, SUM(bytes) tots, 0, 0, \u00a0SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable\n\u00a0 \u00a0 \u00a0 \u00a0 FROM \u00a0 dba_temp_files\n\u00a0 \u00a0 \u00a0 \u00a0 GROUP BY tablespace_name) a, dba_tablespaces b\nWHERE b.tablespace_name = a.tablespace_name\nGROUP BY rollup(a.tablespace_name)\nORDER by a.tablespace_name<\/pre>\n<p>Enjoy!<\/p>\n<p>&nbsp;<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Earlier, provided a small script to find the maximum growable size of a tablespace&#8230; 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&#8230;All size is in MB. SELECT a.tablespace_name TSNAME,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ROUND(SUM(a.growable)\/1048576) growable,\u00a0 \u00a0 \u00a0 &#8230; <a title=\"Tablespace Free Space and Max Size\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2011\/12\/\" aria-label=\"Read more about Tablespace Free Space and 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-12","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\/12","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=12"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/12\/revisions"}],"predecessor-version":[{"id":2959,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/12\/revisions\/2959"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=12"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=12"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=12"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}