{"id":384,"date":"2020-09-04T17:28:00","date_gmt":"2020-09-04T15:28:00","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=384"},"modified":"2020-09-04T11:11:46","modified_gmt":"2020-09-04T09:11:46","slug":"how-to-reclaim-storage-space-on-oracle-autonomous-database","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2020\/09\/04\/how-to-reclaim-storage-space-on-oracle-autonomous-database\/","title":{"rendered":"How to reclaim storage space on Oracle Autonomous Database"},"content":{"rendered":"\n<p>Man shall pay only for what it uses. This is also a motto of the Cloud and Oracle with second-level billing pushes this model.<\/p>\n\n\n\n<p>Concerning disk space, however, it is not always easy. While terabyte prices are getting cheaper, sometimes you make a big cleanup of your database and then you would like to pay only for what is being used.<\/p>\n\n\n\n<p>On Oracle Autonomous Databases it is the sum of datafiles size that counts.<\/p>\n\n\n\n<p>Image now that you have a huge table and then drop it. The datafile space is not recovered.<\/p>\n\n\n\n<p>In order to recover space you need:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Purge the recycle bin:<\/li><\/ul>\n\n\n\n<p><code>SQL&gt; purge dba_recyclebin<\/code><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>reduce size of DATA tablespace datafile <\/li><\/ul>\n\n\n\n<p><code>SQL&gt; alter database datafile &lt;file_id&gt; resize yyyM;<\/code><\/p>\n\n\n\n<p>Now, this will be possible only if there are no extents used at the end of the datafile. Otherwise, one can try to <code>alter table &lt;table_name&gt; move online;<\/code> and then <code>alter tablespace &lt;tbs_name&gt; coalesce;<\/code> but this is not sure to help.<\/p>\n\n\n\n<p>During my tests I only had one table, which made things easier.<\/p>\n\n\n\n<p>Let&#8217;s hope that Oracle either changes the way to calculate the space used or provides a way to (continuous) defragment a datafile and make the size dynamic.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"476\" height=\"299\" src=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2020\/09\/image.png\" alt=\"\" class=\"wp-image-385\" srcset=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2020\/09\/image.png 476w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2020\/09\/image-300x188.png 300w\" sizes=\"auto, (max-width: 476px) 100vw, 476px\" \/><\/figure>\n\n\n\n<p>To check the storage used on Autonomous Database and find the datafile file_id, you can run the following query:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- Get Space used by tablespace and file_id\nselect TBS &quot;File_ID-Tablespace&quot;,\n  round(sum(bytes)\/1024\/1024\/1024,2) USED_GB,\n  round(sum(bytes)\/max(PROPERTY_VALUE)*100) PCT \nfrom\n  (select file_id||&#039;-&#039;||tablespace_name TBS, bytes \n    from DBA_DATA_FILES\n    where tablespace_name!=&#039;SAMPLESCHEMA&#039;),\n  (select PROPERTY_VALUE \n    from DATABASE_PROPERTIES \n    where PROPERTY_NAME = &#039;MAX_PDB_STORAGE&#039;)\ngroup by rollup(TBS);\n\nFILE_ID-TABLESPACE USED_GB PCT \n------------------ ------- --- \n3252-SYSTEM           0.41   2 \n3253-SYSAUX           3.16  16 \n3254-UNDOTBS1         0.44   2 \n3255-DATA              0.1   0 \n3256-DBFS_DATA         0.1   0 \n                       4.2  21 \n\n-- Get Total space used by DB\nselect round(USED_BYTES\/1024\/1024\/1024,2) USED_GB,\n  round(MAX_BYTES\/1024\/1024\/1024,2) MAX_GB,\n  round(USED_BYTES\/MAX_BYTES*100,2) PCT_USED \nfrom\n  (select PROPERTY_VALUE MAX_BYTES\n    from DATABASE_PROPERTIES \n    where PROPERTY_NAME = &#039;MAX_PDB_STORAGE&#039;),\n  (select sum(BYTES) USED_BYTES\n    from DBA_DATA_FILES \n    where TABLESPACE_NAME != &#039;SAMPLESCHEMA&#039;);\n\nUSED_GB MAX_GB PCT_USED \n------- ------ -------- \n    4.2     20    21.01 \n<\/pre><\/div>\n\n\n<p class=\"has-luminous-vivid-orange-color has-text-color\"><em>.anjo<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Man shall pay only for what it uses. This is also a motto of the Cloud and Oracle with second-level billing pushes this model. Concerning disk space, however, it is not always easy. While terabyte prices are getting cheaper, sometimes you make a big cleanup of your database and then you would like to pay [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":385,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[51,52,39],"tags":[],"class_list":{"0":"post-384","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-cloud","8":"category-oci","9":"category-oracle-19c","10":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/384","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=384"}],"version-history":[{"count":8,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/384\/revisions"}],"predecessor-version":[{"id":394,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/384\/revisions\/394"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media\/385"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}