How to solve the problem of insufficient tablespace, that is, the maximum number of data files

In actual service applications, Oracle may encounter a common problem, that is, insufficient table space, as the amount of data increases. The essence of insufficient tablespace is that the size of data stored in a data file reaches the maximum size of the data file.

If, unfortunately, we encounter insufficient table space problem, what should we do?

SQL > select * from tablespaces where tablespaces cannot be found; SQL > select * from tablespaces where tablespaces cannot be found;

SELECT UPPER(f.tablespace_name)" tablespace name ", d.ot_grootte_MB "tablespace size (M)", d.ot_grootte_MB -f.total_bytes" Used space (M)", TO_CHAR (ROUND ((which OT_GROOTTE_MB - F.T OTAL_BYTES)/which OT_GROOTTE_MB * 100, 2), '990.99') | | '%' "use ratio (%)", 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 F.TABLESPACE_NAMECopy the code

The results are as follows:

 

SQL > select datafile, datafile, datafile, datafile, datafile, datafile, datafile, datafile, datafile, datafile, datafile, datafile, datafile, datafile;

Select t. tablespace_name, -- namespace name t. file_name, -- file name t. autoextensible, -- whether to automatically extend t. tablespace_name / 1024/1024, -- The initial size of the tablespace. Maxbytes / 1024/1024, -- The maximum b.contents size of the tablespace, -- Tablespace type B.xtent_management -- TABLESPACE management mode from dba_data_files t, dba_tablespaces b where t.TABLESPACE_NAME = b.TABLESPACE_NAMECopy the code

The results are as follows:

 

The size of the data file users01.dbf corresponding to the USERS tablespace is only 5M. Here is the size of the data file is obviously too small, not how long would be a shortage of space (this could be automatically extended data file, and a maximum of 3 g, how could a table space is insufficient, it is because in some things will be carried out in large quantities of data processing, especially the data import, or the process of migration, To insert a large amount of data, the available space is only 5M, which is obviously too small, so there will be insufficient table space.

There are two ways to solve the problem of insufficient tablespace:

3.1. The data file users01.dbf can be extended with the following code:

 alter database datafile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF'
 resize 100M ;
Copy the code

You can add another data file users02.dbf directly, initialize the size of the data file with 100M, automatically expand, the maximum size of the data file is 500M, the code is as follows:

 alter tablespace USERS
add datafile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS02.DBF'
size 100m
autoextend on next 10m maxsize 500m
Copy the code

Query the newly added and changed data files again through the query data file usage statement, the result is as follows:

 

Summary: This article describes how to solve the problem of insufficient table space, that is, how to expand or increase the size of the data file when it reaches the maximum, so as to fundamentally solve the problem of insufficient table space.