ORACLE system table space SYSTEM is full and cannot expand table space problem solving process

ORACLE system table space SYSTEM is full and cannot expand table space problem solving process


Database connection problem solving process
ORA-02002
ORA-00604
ORA-01653


ERROR at line 1:
ORA-01119: error in creating database file '+DATADG/'
ORA-17502: ksfdcre:4 Failed to create file +DATADG/
ORA-15041: diskgroup "DATADG" space exhausted


On the morning of March 26, 2018, I logged in to the database and found that the connection could not be made. The screenshot of the error message is as follows:





,
Solution process:
1. Analysis: Analysis to determine the problem: According to the error report, it is initially determined that the system table space occupancy rate is too high, which makes it impossible to connect to the database normally


2. Login: Unable to log in through PLSQL, use XShell to connect to the server remotely, use sqlplus to log in to the sysdba of the sys user
sqlplus sys/[email protected]:1521/DBNAME as sysdba


2. Query: By querying the table space, it is found that the SYSTEM table space occupies 99.99%, and the free space remains 0M
Table space usage query SQL
select f.tablespace_name tablespace_name,
round((d.sumbytes / 1024 / 1024 / 1024), 2) total_g,
round(f.sumbytes / 1024 / 1024 / 1024, 2) free_g,
round((d.sumbytes - f.sumbytes) / 1024 / 1024 / 1024, 2) used_g,
round((d.sumbytes - f.sumbytes) * 100 / d.sumbytes, 2) used_percent
from (select tablespace_name, sum(bytes) sumbytes
from dba_free_space
  group by tablespace_name) f,
(select tablespace_name, sum(bytes) sumbytes
from dba_data_files
where tablespace_name in ('SYSTEM')
  group by tablespace_name) d
where f.tablespace_name = d.tablespace_name  
order by used_percent desc,d.tablespace_name;




3. Expand the table space capacity
By increasing the database system tablespace data problem file size alter tablespace SYSTEM add datafile '+DATADG/' size 1024m, the error content is
ERROR at line 1:
ORA-01119: error in creating database file '+DATADG/'
ORA-17502: ksfdcre:4 Failed to create file +DATADG/
ORA-15041: diskgroup "DATADG" space exhausted


According to the prompt, it is determined that the storage capacity is insufficient, and a file with a size of 1024M cannot be created. After modifying the expansion capacity, an error is reported. The screenshot is as follows:



Query storage capacity size

It is found that the storage Free_MB remains 96M (here is the storage device, which is equivalent to the remaining capacity of the hard disk)
Therefore, it is clear that the main reason for the problem is that the system table space SYSTEM is full, and the expansion table space cannot be expanded due to insufficient storage capacity, and the table space cannot be expanded.


In this case, there are two solutions:
Method 1: Free up storage space by releasing resources occupied by other tablespaces, and then expand the SYSTEM tablespace
Method 2: Free the space of SYSTEM itself by deleting the data in the SYSTEM tablespace


This solution adopts method 2, delete the SYS.AUD$ table, that is, execute truncate table SYS.SUD$The
SYS.SUD$ table stores system audit information, which is generally not used and can be deleted directly.


After deleting, check the table space occupancy rate is only 16.17%
 


Login database test, normal login, problem solved.


With this, the problem is solved.However, it is not a long-term solution. It is necessary to expand storage hardware resources and capacity in a timely manner to prevent this problem from happening again.