Oracle dba_data_files data dictionary with bytes greater than MaxBytes

Today, after work, students sent one, meaning that the bytes value in Dba_data_files is greater than MaxBytes value. As follows:

It feels bad right away, and according to previous Oracle operations experience it is absolutely impossible. But that's what the data looked for. So on Baidu search related issues, no resistance to search out are not very relevant information. So directly on the MoS looking for information, there is really an article about this Value in BYTES Column Greater than maxbytes column in Dba_data_files (document ID 197244.1) and there is a sentencethe BYTES column in Dba_data_files have a value greater than maxbytes column when a datafile is manually resized to A value GREATER than MAXSIZE (maxbytes). Now that's clear, manual resize data files can cause this behavior.

Try to restore it below

The platform for this test is 11.2.0.3

--Create a test table space
SQL> create tablespace zx1 datafile ‘/opt/bboss/tst4/oracle/arch/zx1.dbf’ size 10M;

Tablespace created.
-View the properties of the data file
SQL> col file_name for a100
SQL> set linesize 200
SQL> set num 20
SQL> select file_name, tablespace_name, bytes, maxbytes, autoextensible from dba_data_files where tablespace_name = ‘ZX1’;

FILE_NAME TABLESPACE_NAME BYTES MAXBYTES AUT
-------------------------------------------------- -------------------------------------------------- ------------------------------ ---------- ---------- ---
/opt/bboss/tst4/oracle/arch/zx1.dbf ZX1 10485760 0 NO
-See that the automatic growth of the default data file is NO, and the bytes are only 10M specified when they are created
-Convert data files to automatic growth
SQL> alter database datafile ‘/opt/bboss/tst4/oracle/arch/zx1.dbf’ autoextend on;

Database altered.

SQL> select file_name, tablespace_name, bytes, maxbytes, autoextensible from dba_data_files where tablespace_name = ‘ZX1’;

FILE_NAME TABLESPACE_NAME BYTES MAXBYTES AUT
-------------------------------------------------- -------------------------------------------------- ------------------------------ -------------------- -------------------- ---
/opt/bboss/tst4/oracle/arch/zx1.dbf ZX1 10485760 34359721984 YES
-MAXBYTES defaults to 32G after automatic growth is turned on, that is, data files will not expand after expanding to 32G
-Test is automatically expanded
--Create test users and tables
SQL> create user zhaoxu identified by zhaoxu;

User created.

SQL> grant connect, resource to zhaoxu;

Grant succeeded.

SQL> create table zhaoxu.test1 tablespace zx1 as select * from dba_objects;

Table created.
--Cycle insert data
SQL> begin
  2 for i in 1..100
  3 loop
  4 insert / * append * / into zhaoxu.test1 select * from zhaoxu.test1;
  5 commit;
  6 end loop;
  7 end;
  8  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table ZHAOXU.TEST1 by 8192 in tablespace ZX1
ORA-06512: at line 4
-An error is reported after a period of execution, the table space cannot be expanded
--View data file size
SQL> select file_name, tablespace_name, bytes, maxbytes, autoextensible from dba_data_files where tablespace_name = ‘ZX1’;

FILE_NAME TABLESPACE_NAME BYTES MAXBYTES AUT
-------------------------------------------------- -------------------------------------------------- ------------------------------ -------------------- -------------------- ---
/opt/bboss/tst4/oracle/arch/zx1.dbf ZX1 34293743616 34359721984 YES
--Look at the size of the data file on the operating system
SQL>! Du -sk /opt/bboss/tst4/oracle/arch/zx1.dbf
33490016 /opt/bboss/tst4/oracle/arch/zx1.dbf
-You can see that the data file growth does not exceed the 32G specified by MAXBYTES
--At this time resize the data file
SQL> alter database datafile ‘/opt/bboss/tst4/oracle/arch/zx1.dbf’ resize 33554424k;-due to operating system limitations, the maximum can only be 33554424k, but enough to explain the problem

Database altered.

SQL> select file_name, tablespace_name, bytes, maxbytes, bytes-maxbytes, autoextensible from dba_data_files where tablespace_name = ‘ZX1’;

FILE_NAME TABLESPACE_NAME BYTES MAXBYTES BYTES-MAXBYTES AUT
-------------------------------------------------- -------------------------------------------------- ------------------------------ -------------------- -------------------- -------------------- ---
/opt/bboss/tst4/oracle/arch/zx1.dbf ZX1 34359730176 34359721984 8192 YES
--Now see bytes> maxbytes of the data file
The process of BYTES> MAXBYTES is restored above.

The data files of the table space mentioned above are automatically expanded. What about data files that are not automatically expanded?

Continue to test below

--Cancel the automatic expansion of data files
SQL> alter database datafile ‘/opt/bboss/tst4/oracle/arch/zx1.dbf’ autoextend off;

Database altered.

SQL> select file_name, tablespace_name, bytes, maxbytes, bytes-maxbytes, autoextensible from dba_data_files where tablespace_name = ‘ZX1’;

FILE_NAME TABLESPACE_NAME BYTES MAXBYTES BYTES-MAXBYTES AUT
-------------------------------------------------- -------------------------------------------------- ------------------------------ -------------------- -------------------- -------------------- ---
/opt/bboss/tst4/oracle/arch/zx1.dbf ZX1 34359730176 0 34359730176 NO
-You can see that MAXBYTES has become 0
What happens if the data file is changed to automatically expandable?

SQL> alter database datafile ‘/opt/bboss/tst4/oracle/arch/zx1.dbf’ autoextend on;

Database altered.

SQL> select file_name, tablespace _name, bytes, maxbytes, bytes-maxbytes, autoextensible from dba_data_files where tablespace_name = ‘ZX1’;

FILE_NAME TABLESPACE_NAME BYTES MAXBYTES BYTES-MAXBYTES AUT
-------------------------------------------------- -------------------------------------------------- ------------------------------ -------------------- -------------------- -------------------- ---
/opt/bboss/tst4/oracle/arch/zx1.dbf ZX1 34359730176 34359730176 0 YES
--You can see BYTES = MAXBYTES
Summarized below:

There is no inevitable connection between the bytes value in dba_data_files data dictionary and maxbytes

When the data file cannot be automatically expanded, MAXBYTES = 0, the value of BYTES is both the size of the file actually allocated and the maximum size of the file

When the data file can be automatically expanded, the default MAXBYTES = 32G, BYTES is the size of the file actually allocated, by default BYTES will not exceed MAXBYTES, if you resize the file, then BYTES may be greater than MAXBYTES.

This article is from the "DBA Fighting!" Blog, please be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1882495

The bytes in the Oracle dba_data_files data dictionary are greater than maxbytes