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

Background: the table space has been set to grow automatically, but it will still be reported to ORA-01653 when it reaches a certain size.

Resolution process:

Access to relevant information, most only give a solution, but this solution has been verified is also temporary, to a certain size will still report an error, and then can only take the same method to operate again.

Through the observation of several machines, it is found that when the error is reported, the size of the data table file is the same as 32G. Therefore, it is judged that there is some reason for the restriction. So conduct a relevant search, and finally found the answer, but, for the description of this problem, there are two claims: 1, under the windows system, a single file has a 32G limit, but I did not find strong evidence to support this claim, even if others mentioned it, but did not give a specific reason, the second argument is the problem of oracle maxsize. Personally, I think the second statement is correct.

The cause of the problem:

When creating the database, the default 8kb is used for the database block size, and the relationship of the capacity DB_BLOCK_SIZE of the table file in oracle is maxsize=DB_BLOCK_SIZE × 4194304bot 4G. The result happens to be 32G, that is, when the block size is 8kb, the maximum size of a single file is 32G, thus the cause of the problem is found. The file size limits for various block sizes are given below.

4k:16384M

8K:32768M

16k:65536M

32K:131072M

64k:262144M

Solution:

1. Add data files: alter tablespace add datafile'/ XX/XXX/XXX/XXx.dbf'size XXM,AUTOEXTEND ON NEXT xM MAXSIZE xxxM; this method can temporarily solve the problem.

2. Use large files. Large files are supported after oracle10g. However, a large file has only one data file, which cannot be extended by adding a data file after creation. Please consult the data for other features.

3. Database reconstruction, EXP exports data, rebuilds database, and selects appropriate block size during installation.

Summary

It is necessary to make a good plan before creating the database, and have an estimate of the size of the data file, otherwise it may bring unexpected consequences. In addition, the study of Oracle needs to be systematic in order to avoid this kind of failure as far as possible.

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.