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

This article will explain in detail what to do when a single ORA-1653 oracle data file exceeds the maximum limit. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

In the morning, the developer reported that the log could not be filled in the database table, indicating that the database ORA-1653 error.

The database alarm log is queried. The error is as follows:

ORA-1653: unable to extend table CMTSMAIN.T0501_LOG by 128 in tablespace CMTSMAIN_TS

ORA-1653: unable to extend table CMTSMAIN.T0501_LOG by 8192 in tablespace CMTSMAIN_TS

ORA-1653: unable to extend table CMTSMAIN.T0502_LOG by 128 in tablespace CMTSMAIN_TS

ORA-1653: unable to extend table CMTSMAIN.T0502_LOG by 8192 in tablespace CMTSMAIN_TS

Query database tablespace usage as follows

SQL > select total.tablespace_name,round (total.MB,2) as Total_MB,round (total.MB-free.MB, 2) as Used_MB,round ((1-free.MB/total.MB) * 100,2) as Used_Pct from (select tablespace_name, sum (bytes) / 1024 as MB from dba_data_files group by tablespace_name) free, (select tablespace_name, sum (bytes) / 1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name=total.tablespace_name

TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT

CMTSMAIN_TS 32757.06 32730 99.92

The utilization rate of table space is 99.92%.

Query data files for tablespaces and automatically extend properties

SQL > select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE from dba_data_files

TABLESPACE_NAME FILE_NAME AUT

CMTSMAIN_TS / u01/data/orcl/CMTSMAIN_TS01.dbf YES

This tablespace contains only one data file, and the file size has reached the maximum limit for a single data file in oracle.

Therefore, add a data file

SQL > alter tablespace CMTSMAIN_TS add datafile'/ u01 size data autoextend on CMTSMAING TS02.dbf'

After communicating with the R & D staff, there are two tables in this tablespace for a large amount of data insertion, so the data file is increased to 30G.

SQL > alter database datafile'/ u01 resize resize 30G

Continue to add two data files at the same time

SQL > alter tablespace CMTSMAIN_TS add datafile'/ u01 size data autoextend on CMTSMAING TS03.dbf'

SQL > alter tablespace CMTSMAIN_TS add datafile'/ u01 size data autoextend on CMTSMAING TS04.dbf'

View database tablespace usage

SQL > select total.tablespace_name,round (total.MB,2) as Total_MB,round (total.MB-free.MB, 2) as Used_MB,round ((1-free.MB/total.MB) * 100,2) as Used_Pct from (select tablespace_name, sum (bytes) / 1024 as MB from dba_data_files group by tablespace_name) free, (select tablespace_name, sum (bytes) / 1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name=total.tablespace_name

TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT

CMTSMAIN_TS 124917.06 34269 27.43

Knowledge points are involved. The maximum size of a single data file is 32G. The principle is as follows.

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (2 ^ 22) blocks.

The database block size is 8k.

SQL > show parameter block

NAME TYPE VALUE

-

Db_block_size integer 8192

Then the largest data file size that can be created is 2 ^ 22 * 8K=32G, while a single data file has reached 32 gigabytes, which exceeds the limit and cannot be extended, so an error is reported. 2 ^ 22 is due to the use of 22 bits in Oracle's Rowid to represent Block numbers, which can only represent a maximum of 2 ^ 22-1 (4194303) blocks.

And: in order to expand the size of the data file, Oracle10g introduces a large file tablespace. Under the large file tablespace, Oracle uses 32 bits to represent the Block number, that is, each file can hold up to 4G Block under the large file tablespace.

That is to say, when Block_size is 8k, the data file can reach 32T.

About the ORA-1653 oracle single data file exceeds the maximum limit how to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.