Daily Archives: 04.09.2020

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 

.anjo