Why Oracle ORA-01653 table space does not grow automatically and how to handle it

Background: The table space has been set to grow automatically, but will still be reported ORA-01653 after reaching a certain size.

Resolution process:

Access to the relevant information, most of the solutions, but this solution has been verified to be temporary, to a certain size will still error, to the time only to take the same approach to operate.

Observations from several machines revealed that the size of the data table file was 32G consistent when the error was reported. It is therefore judged that the limitation is caused by some reason. So the relevant search, and finally found the answer, but, for the description of the problem, there are 2 statements: 1, Windows system, the single file has a 32G limit, but I did not find strong support evidence, even if other people mention the statement, but did not give specific reasons, The second argument is the Oracle maxsize problem. In my opinion, the second argument is the right one.

The cause of the problem:

When you create a database, the database block size uses the default 8KB, and the capacity of the table file in Oracle is db_block_size maxsize=db_block_sizex4194304; 4194304=4g. The result is exactly 32G, that is, the size of the data block at 8KB, the maximum size of a single file is 32G, resulting in the cause of the problem found. Below are the limitations of the file size under various block sizes.

4k:16384m

8k:32768m

16k:65536m

32k:131072m

64k:262144m

Workaround:

1. Add data file: Alter tablespace add datafile '/xx/xxx/xxx/xxx.dbf ' size xxm,autoextend on NEXT xM MAXSIZE xxxm, this method can temporarily resolve the problem.

2, the use of large files. Large files are supported after oracle10g. However, the large file has only one data file, cannot be expanded by adding data files after creation, other features please check the data yourself.

3, Database reconstruction, exp Export data, rebuild the database, install the appropriate data block size.

Summarize

Before creating a database, you need to plan for the size of the data file to have an estimate, otherwise it may have unintended consequences, in addition to the Oracle learning needs to be systematized to minimize the occurrence of such failures.

This article is from the "12274138" blog, please be sure to keep this source http://12284138.blog.51cto.com/12274138/1876215

Why Oracle ORA-01653 table space does not grow automatically and how to handle it