Shulou(Shulou.com)06/01 Report--

At the end of the day, my classmate sent me a screenshot saying that the value of bytes in dba_data_files is greater than the value of maxbytes. The screenshot is as follows:

Immediately feel bad, according to the previous Oracle operation and maintenance experience that this is absolutely impossible. But that's what the data show. So hundreds of degrees search related questions, can not bear to search out are not very relevant information. So go directly to MOS to find information, there is really an introduction to this Value in BYTES Column Greater than MAXBYTES Column in DBA_DATA_FILES (document ID 197244.1), in which there is such a sentence 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). So now I understand that manual resize data files can cause this phenomenon.

Let's start to restore it.

The platform for this test is 11.2.0.3

-- create a test tablespace SQL > create tablespace zx1 datafile'/ opt/bboss/tst4/oracle/arch/zx1.dbf' size 10m each Tablespace created.-- to view the properties of the data file SQL > col file_name for a100SQL > set linesize 200SQL > set num 20SQL > 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-- sees that the default data file automatically grows to NO Bytes also has only 10M alter database datafile specified at the time of creation-convert the data file to auto-growing 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-- sees that after automatic growth is enabled, MAXBYTES defaults to 32G That is, after the data file is extended to 32G, it is no longer extended-- the test is automatically extended-- and the test user and table SQL > create user zhaoxu identified by zhaoxu are created. User created.SQL > grant connect, resource to zhaoxu;Grant succeeded.SQL > create table zhaoxu.test1 tablespace zx1 as select * from dba_objects;Table created.-- 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 ZX1ORA-06512: at line 4murf-error is reported after execution for a period of time, tablespace cannot be expanded-view datafile 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 data file size on the operating system SQL >! du-sk / opt/bboss/tst4/oracle/arch/zx1.dbf33490016 / opt/bboss/tst4/oracle/arch/zx1.dbf-- can see that the growth of the data file does not exceed the 32G SQL specified by MAXBYTES-at this time the resize data file SQL > alter database datafile'/ opt/bboss/tst4/oracle/arch/zx1.dbf' resize 33554424k -- due to the operating system limit, the maximum limit is 33554424k, but it is 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 you can see the bytes > maxbytes of the data file

The process of BYTES > MAXBYTES is restored above.

The data files in the table space mentioned above are automatically extended, so what about the data files that are not automatically extended?

Let's continue the test.

-- cancel the automatic extension 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 034359730176 NO-- you can see that MAXBYTES has changed to 0

What happens if you change the data file to be 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 343597301760 YES-- you can see the BYTES=MAXBYTES.

The following is a summary:

The bytes value in the dba_data_files data dictionary is not necessarily related to maxbytes.

When a data file cannot be automatically extended, the value of MAXBYTES=0,BYTES is both the actual allocated size of the file and the maximum size of the piece

When the data file can be automatically extended, the default MAXBYTES=32G,BYTES is the size that the file has actually allocated. By default, BYTES will not exceed MAXBYTES. If you resize the file, then BYTES may be greater than MAXBYTES.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.