This is the 13th day of my participation in the August More Text Challenge

After Oracle is deployed in the production environment, if the parameters are not optimized and there is no dedicated person to perform daily operation and maintenance of the database, it is difficult to avoid such an error. The more frequent the service is, the faster the service will be triggered. After Oracle database is built, you can refer to juejin.cn/post/699141…

1. Questions:

The database connection is slow or an error is reported, similar to the following:

ORA-00604: error occurred at recursive SQL level 1 
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM 
ORA-02002: error while writing to audit trail 
ORA-00604: error occurred at recursive SQL level 1 
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM 
Copy the code

2. Reasons:

The Oracle System tablespace is exhausted. The system tablespace can be automatically expanded to 32GB by default.

1) Business data is stored in the system.

2) The Audit of the Oracle database is enabled, and the system tablespace is overloaded with audit logs.

3. Solutions:

Applicable to both Windows and Linux environments.

1) Move business data to another business table space.

2) Clear audit logs.

Querying table space usage (including extensibility)

set linesize 150 set pagesize 500 column file_name format a65 column tablespace_name format a25 column top_no_extend format a13 column top_extend format a10 column free_extend format a11 column used format a8 column used_extend format a11 select f.tablespace_name tablespace_name, to_char(round((d.sumbytes/1024/1024/1024),2)) || 'GB' top_no_extend, to_char(round(((d.sumbytes+d.extend_bytes)/1024/1024/1024),2)) || 'GB' top_extend, To_char (round ((f.s umbytes + d.E xtend_bytes) / 1024/1024/1024, 2)) | | 'GB' free_extend, To_char (round ((d.s umbytes - f.s umbytes) / 1024/1024/1024, 2)) | | 'GB, informs, to_char(round((d.sumbytes-f.sumbytes)*100/(d.sumbytes+d.extend_bytes),2)) || '%' as used_extend from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f, (select tablespace_name,sum(aa.bytes) sumbytes,sum(aa.extend_bytes) extend_bytes from (select nvl(case when autoextensible ='YES' then (maxbytes-bytes) end,0) Extend_bytes ,tablespace_name,bytes from dba_data_files) aa group by tablespace_name) d where f.tablespace_name= d.tablespace_name order by used_extend desc;Copy the code

The system tablespace is used up:

Clearing the audit log table sys.aud$

truncate table sys.aud$;
Copy the code

SQL > alter table tablespace system;

4. Suggestions for improvement:

Disabling database Audit

Show parameter audit -- Disable database audit ALTER system set audit_TRAIL =none scope=spfile; Run the show parameter audit command to check the database audit statusCopy the code

Restart the database to take effect

Shu immediate -- start database startup -- Restart database show parameter auditCopy the code