ORACLE ORA-01653: how to handle the error of unable to extend table

This article mainly introduces the relevant knowledge of how to deal with the ORACLE ORA-01653: unable to extend table error. The content is detailed and easy to understand, the operation is simple and fast, and it has certain reference value. I believe you have finished reading this article.ORACLE ORA-01653: Unable to extend table error How to deal with the article will be beneficial, let's take a look.

A few days ago, when the project was running, the database data was inserted suddenly:

ORA-01653: unable to extend table HSDC.ADDRESS by 128 in tablespace DATACENTER

It probably means that the HSDC table space is insufficient. After searching the Internet, there are generally two situations in which the problem of insufficient table space occurs: one is that the automatic expansion function of the table space is not turned on; the other is indeedThe table space is really not enough, and it has reached the limit of expansion.Therefore, to solve the problem, we should first check the size of the Oracle table space, and then check whether the automatic expansion function of the Oracle table space is turned on; if the table space is indeed insufficient, we need to expand the table space;

The specific steps are as follows:

1. In Navicat the following commands are straight:

SELECT a.tablespace_name "tablespace name", a.bytes / 1024 / 1024 "tablespace size (M)", (a.bytes - b.bytes) / 1024 / 1024"Used space (M)", b.bytes / 1024 / 1024 "Free space (M)", round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "Usage ratio"FROM (SELECT tablespace_name, sum(bytes) bytesFROM dba_data_filesGROUP BY tablespace_name) a,(SELECT tablespace_name, sum(bytes) bytes, max(bytes) largestFROM dba_free_spaceGROUP BY tablespace_name) bWHERE a.tablespace_name = b.tablespace_nameORDER BY ((a.bytes -b.bytes) / a.bytes) DESC

"Table space size (M)" indicates the total size of disk space occupied by all data files in the table space in the operating system;

For example: HSDC table space has two data files, datafile1 is 300M, datafile2 is 400M, then the "table space size" of HSDC table space is 700M.

"Used space (M)" indicates how much the tablespace has been used;

"Free space" indicates how much table space is left;

"Usage ratio" indicates the percentage that has been used;

2, for example, from step 1, you can check that the HSDC table space has been used more than 90%, and you can check that the table space has several data files, whether each data file is automatically expanded, and the maximum value that can be automatically expanded.

SELECT file_name,tablespace_name,bytes / 1024 / 1024 "bytes MB",maxbytes / 1024 / 1024 "maxbytes MB"FROM dba_data_filesWHERE tablespace_name = 'HSDC';

3. Check whether the DATACENTER tablespace is automatically extended;

SELECT file_id, file_name, tablespace_name, autoextensible, increment_byFROM dba_data_filesWHERE tablespace_name = 'HSDC'ORDER BY file_id desc;

Check whether the corresponding value of "autoextensible" is YES or NO. If it is NO, it means that the automatic extension function of the DATACENTER tablespace is not enabled.Just change it to YES.

4. If the table space is not enough, it is almost full, we need to expand the table space

There are two types of expansion table empty construction: one is to increase the size of the data file, and the other is to increase the number of data files;

First find out the data file and path corresponding to the tablespace:

SELECT * FROM dba_data_files t WHERE t.tablespace_name='TBS_DATACENTER';

Solution 1: Increase the data file

alter database datafile 'full path data file name' resize ***M; #Increase the size of a data file in the corresponding tablespace ***M

Solution 2: Add a data file

Get the statement to create the tablespace data file:

SELECT dbms_metadata.get_ddl('TABLESPACE', 'USER_DATA') FROM dual;

①. Confirm that the disk space is sufficient and add a data file

alter tablespace tablespace name add datafile 'full path data file name' size ***Mautoextend on maxsize 20G;

--Add a new data file, the full path data file name is the full path file name of the new data file.The size is ***M, the automatic extension function is turned on, and the maximum extension value of the data file is 20G.

②、Verify the added data file

SELECT file_name, file_id, tablespace_nameFROM dba_data_filesWHERE tablespace_name = 'tablespace name'

Note: If the statement to delete the tablespace data file is:

altertablespace tablespace name DROP datafile '/u01/app/oracle/product/10.2.0/oradata/orcl/HSDC.dbf '

PS: The table space generally keeps the idle percentage above 10%, and the data file size should not exceed 2G.When the table space is insufficient, either resieze datafile, or increase datafile.

The content of this article about "ORACLE ORA-01653: how to deal with the unable to extend table error" is introduced here, thank you for reading!I believe that everyone has a certain understanding of the knowledge of "ORACLE ORA-01653: how to deal with the unable to extend table error". If you want to learn more, please pay attention to the Yisu Cloud Industry Information Channel.