How to reclaim storage space on Oracle Autonomous Database
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 only for what is being used.
On Oracle Autonomous Databases it is the sum of datafiles size that counts.
Image now that you have a huge table and then drop it. The datafile space is not recovered.
In order to recover space you need:
- Purge the recycle bin:
SQL> purge dba_recyclebin
- reduce size of DATA tablespace datafile
SQL> alter database datafile <file_id> resize yyyM;
Now, this will be possible only if there are no extents used at the end of the datafile. Otherwise, one can try to
alter table <table_name> move online; and then
alter tablespace <tbs_name> coalesce; but this is not sure to help.
During my tests I only had one table, which made things easier.
Let’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.
To check the storage used on Autonomous Database and find the datafile file_id, you can run the following query:
-- Get Space used by tablespace and file_id select TBS "File_ID-Tablespace", round(sum(bytes)/1024/1024/1024,2) USED_GB, round(sum(bytes)/max(PROPERTY_VALUE)*100) PCT from (select file_id||'-'||tablespace_name TBS, bytes from DBA_DATA_FILES where tablespace_name!='SAMPLESCHEMA'), (select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME = 'MAX_PDB_STORAGE') group by rollup(TBS); FILE_ID-TABLESPACE USED_GB PCT ------------------ ------- --- 3252-SYSTEM 0.41 2 3253-SYSAUX 3.16 16 3254-UNDOTBS1 0.44 2 3255-DATA 0.1 0 3256-DBFS_DATA 0.1 0 4.2 21 -- Get Total space used by DB select round(USED_BYTES/1024/1024/1024,2) USED_GB, round(MAX_BYTES/1024/1024/1024,2) MAX_GB, round(USED_BYTES/MAX_BYTES*100,2) PCT_USED from (select PROPERTY_VALUE MAX_BYTES from DATABASE_PROPERTIES where PROPERTY_NAME = 'MAX_PDB_STORAGE'), (select sum(BYTES) USED_BYTES from DBA_DATA_FILES where TABLESPACE_NAME != 'SAMPLESCHEMA'); USED_GB MAX_GB PCT_USED ------- ------ -------- 4.2 20 21.01