dba_data_files reporting bigger value in bytes column than maxbyte column

<<Back to DB Administration Main Page

BYTES column value is greeter than MAXBYTES column in DBA_DATA_FILE table

I come across a situation today where I extended a BIG FILE tablespace. After extension tried to verify the new size and got following result.

SQL > SELECT file_id,
        ROUND(bytes/1024/1024/1024) AS size_gb,
        ROUND(maxbytes/1024/1024/1024) AS max_size_gb,
        autoextensible,
        increment_by,
        status
 FROM   dba_data_files
 where TABLESPACE_NAME='DATA'
 ORDER BY file_name

   FILE_ID  SIZE_GB   MAX_SIZE_GB     AUT                INCREMENT_BY       STATUS
----------      ----------     -----------                   ---                    ------------                     ---------
        34      256           128                         YES                 2048                          AVAILABLE

1 row selected.

Value in BYTES Column Greater than MAXBYTES Column in DBA_DATA_FILES;

After spending 5 min I found the justification and workaround mentioned in Doc ID 197244.1

Explanation
-----------
The MAXBYTES column in DBA_DATA_FILES is not updated when the datafile has been
resized. The only column that is updated is BYTES. The MAXBYTES is updated by
the ALTER DATABASE command with MAXSIZE option as described above.

Workaround 

SQL> alter database datafile 34  autoextend on maxsize 256G;

Database altered.

SQL>  SELECT file_id,
  ROUND(bytes/1024/1024/1024) AS size_gb,
        ROUND(maxbytes/1024/1024/1024) AS max_size_gb,
        autoextensible,
        increment_by,
        status
 FROM   dba_data_files
 where TABLESPACE_NAME='DATA'
 ORDER BY file_name ;

   FILE_ID    SIZE_GB MAX_SIZE_GB    AUT     INCREMENT_BY    STATUS

        34          256                        256               YES            2048                         AVAILABLE

1 row selected.