Oracle – Resolving Inconsistency Between maxbytes and bytes in Tablespaces

disk-spaceoracletablespaces

Query:

Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1, 
ROUND (SUM (d.maxbytes) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME'

Output:

size1: 70, size2: 64

As you can see the max bytes allowed for the data files for a particular table-space is only 64GB however it has somehow extended to 70GB!!

Scenario: At a given point the table-space got completely filled (64GB) and I saw 100% used in one of my routine checks. So I went ahead and temporarily truncated some unwanted tables to free up some space. I didn't encounter the cannot extend error anywhere in any of my processes (although it might have occurred) but what bugs me is that the total size of the table-space just increased from 64GB(max) where it should have stopped to 70(gb).

Please advise how it could have happened or any option I have missed !! and where is the extra 6 GB being stored.

Thanks in advance.

Edit:

Output of :

select bytes,maxbytes,autoextensible 
  from dba_data_files
 where tablespace_name = 'TABLESPACE_NAME';

15728640000 4294967296  YES

30303846400 32212254720 YES

28626124800 32212254720 YES

Best Answer

MAXBYTES is 0 for datafiles that are not autoextensible, but BYTES is greater than 0. Use this:

Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1, 
ROUND (SUM (decode(d.maxbytes, 0, d.bytes, d.maxbytes)) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME';

But it is possible to manually resize a datafile above maxbytes.

SQL> select file_id, bytes, maxbytes from dba_data_files;

   FILE_ID      BYTES   MAXBYTES
---------- ---------- ----------
         1  954204160 3.4360E+10
         3 1142947840 3.4360E+10
         5  608174080 3.4360E+10
         7    5242880   10485760

SQL> alter database datafile 7 resize 20M;

Database altered.

SQL> select file_id, bytes, maxbytes from dba_data_files;

   FILE_ID      BYTES   MAXBYTES
---------- ---------- ----------
         1  954204160 3.4360E+10
         3 1142947840 3.4360E+10
         5  608174080 3.4360E+10
         7   20971520   10485760

I updated the query:

Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1, 
ROUND (SUM (greates(d.bytes, d.maxbytes)) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME';
Related Question