Oracle error reporting ora-01653: table XX cannot be extended through 8192 (in table space XX? Data)

Time:2020-3-26

1 problem

Insert data into the Oracle 10g database in batches. When nearly 200 million pieces of data are inserted, the following error is reported:
Ora-01653: table XX cannot be extended by 8192 (in tablespace XX? Data).

Looking at the table space, it is found that the size of the table space has reached 32g, but infinite expansion has been set when creating the table space (the initial space is 20g), and the disk space is not full, indicating that the table space cannot be expanded automatically.

2 reasons

Find out the maximum value of Oracle 10g single tablespace data file is:
Maximum data block * DB block size

View DB block size of Oracle

SQL> select value from v$parameter where name =’db_block_size’;


8192

The data block size of the local database is 8K, and the maximum value of a single table space data file of the local Oracle is calculated as follows:
4194304 * 8/1024 = 32768M (32G);

So even if AUTOEXTEND on maxsize unlimited is set when creating a table space, its maximum space will not exceed 32g.

Note:
The file capacity of tablespace data is related to the setting of DB block size, which is specified when the database instance is created. The DB block size parameter can be set to 4K, 8K, 16K, 32K, 64K, etc. the maximum number of Oracle physical files is 4194304 data blocks (this parameter is determined by the operating system, and should be this number generally). The maximum value corresponding relationship of tablespace data files can be calculated by 4194304 × DB block size / 1024m.
4K maximum table space: 16384m

8K maximum table space: 32768m

The maximum table space of 16K is 65536m

32K maximum table space: 131072m

The maximum table space of 64K is: 262144m

By default, Oracle allocates 8K, which corresponds to the space size of 32768m. If you want to continue to increase the table space, you only need to use alter tablespace name add datafile ‘path / file_name’ size 1024m; to add data files.

The data block is the smallest space allocation unit in Oracle. The data of various operations is put here. The read and write of Oracle from disk is also a block. Once the database is created, the DB block size cannot be changed. Because Oracle stores data in blocks, at least one block is occupied by any storage element. If you change the DB block size, some blocks will not work properly.

In fact, in UNIX like operating system, the relationship between file block and Oracle block is very close (it is recommended to be equal), so as to ensure the efficiency of database execution. It may not be so exquisite under windows. It is recommended to use more than 8K blocks, which have been tested. In the same configuration, 8K blocks are about 40% faster than 4K and more than three times faster than 2K.

3 solutions

There are two processing methods: ① if there is only one data file in the current tablespace, you can expand the size of the data file (the maximum size of a single data file is 32g); ② add a new data file for the current tablespace.

Add data files to the current tablespace as follows:

At the command line, log in to Oracle as the Oracle system administrator user, and then do the following:

1) Method 1: step by step. Add a data file for the specified tablespace (three steps)
① Creates a data file for the specified tablespace and specifies the initial size
Alter tablespace tablespace name
Add datafile ‘d: oracleappadministratoratoradataorcl new data file name. DBF’
SIZE 32M;

② Turn on automatic growth for this data file
Alter database datafile ‘d: oracleappadministratoratoradataorcl new data file name. DBF’ AUTOEXTEND on;

③ Specify the size of each automatic growth
Alter database datafile ‘d: oracleappadministratoratoradataorcl new data file name. DBF’ AUTOEXTEND on next 200m;

2) Method 2: one step in place.Add a data file for the specified tablespace(one step in place: specify the initial size, turn on automatic growth, and set the size of each automatic growth)
Alter tablespace tablespace name add datafile ‘d: appadministratoradataorcldatafile new data file name. DBF’ size 10240m autoxtend on next 1024m maxsize unlimited;