How to solve the problem of insufficient database table space

1. The database table space is insufficient. The insert operation will report

conn.msg = ORA-01653: 表 *******表名字********无法通过 8192 (在表空间 USERS 中) 扩展
ORA-06512: 在 "调用的函数", line 58
ORA-01403: 未找到任何数据
 [db.cpp:233]

. The query results are caused by insufficient data table space size.

2. Check some relevant information about the table space occupied

--Query the content occupied by the table space size

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1;

Result:

This is the information size occupied by all table spaces. The space size of the table is all the size information allocated to the table space. If the usage ratio is relatively high, there may be insufficient space and data cannot be inserted.

3. Query the absolute address, size and file ID of the physical disk corresponding to the table space

---Query the data file and data file size

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

## IV. The following All three methods can be used to increase the size of the table space

1. Do not change the previous DBF file directly, and add a file statement to increase the size of the table space. The size of the table space

#--Let the data file automatically expand

alter tablespace users add datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF' size 32760m autoextend on next 1024m maxsize unlimited; 
--segment space management auto extent management local;

It is said that it can Connect the previous sentence and execute it together (just remove the semicolon from the previous sentence). If it is not executed, the execution effect is not known. During the period of caution, use it with caution

## 2. --Let the data file automatically expand. Directly change the table space size in the source file and set it to the form of automatic expansion. The growth rate is automatically expanded to a size of 100m. The maximum expansion is up to 10000m

alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF' autoextend on next 100m maxsize 10000m

3. --Adjust the size of the data file. Re-adjust the content size of the source file. It is not automatically expanded and the size is directly hard-coded.

alter database  datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF'  resize  500M;

5. After performing the above operations in 4. From the query, you can see that the total allocated space has greatly increased and the occupancy rate has decreased.

The above is the detailed content of How to solve the problem of insufficient database table space. For more information, please follow other related articles on the PHP Chinese website!