dba_data_files中BYTES字段大于MAXbytes字段值

郑全

PURPOSE
-------
This document explains why the value in BYTES column of DBA_DATA_FILES can be
GREATER than MAXBYTES value.

SCOPE & APPLICATION
-------------------

This article can be used by DBAs and Support Analysts.

Description
-----------

The BYTES column in DBA_DATA_FILES has a value greater than MAXBYTES column when
a datafile was manually resized to a value GREATER than MAXSIZE (MAXBYTES).
The MAXBYTES column in DBA_DATA_FILES is populated automatically with non-zero
value when a datafile is defined as autoextend ON.
The BYTES column in DBA_DATA_FILES displays the current size of the file in bytes.

Here is an example of how this can happen.

Step-1: Create a New Tablespace
======  =======================

   SQL> create tablespace tst
     2  datafile 'd:\oracle\tst01.dbf' size 5m autoextend on;

   Tablespace created.

   SQL> select file_name, bytes, maxbytes, autoextensible from dba_data_files;

   FILE_NAME                                     BYTES   MAXBYTES AUT
   ---------------------------------------- ---------- ---------- ---
   D:\ORACLE\TST01.DBF                         5242880 1.7180E+10 YES

   1 rows selected.

Step-2: Alter the MAXSIZE value from default value
======  ==========================================

   SQL> alter database datafile 'd:\oracle\tst01.dbf' autoextend on maxsize 10m;

   Database altered.

   SQL> select file_name, bytes, maxbytes,a utoextensible from dba_data_files;

   FILE_NAME                                     BYTES   MAXBYTES AUT
   ---------------------------------------- ---------- ---------- ---
   D:\ORACLE\TST01.DBF                         5242880   10485760 YES

   1 rows selected.


Step-3: Resize the datafile to a value higher than MAXBYTES
======  ===================================================

   SQL> alter database datafile 'd:\oracle\tst01.dbf' resize 20m;

   Database altered.


   SQL> select file_name, bytes, maxbytes, autoextensible from dba_data_files;

   FILE_NAME                                     BYTES   MAXBYTES AUT
   ---------------------------------------- ---------- ---------- ---
   D:\ORACLE\TST01.DBF                        20971520   10485760 YES

   1 rows selected.


The value in BYTES column is GREATER than MAXBYTES.


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.