Shulou(Shulou.com)05/31 Report--

This article mainly explains the "system table space how to solve ORA-00604 error", the article explains the content is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "system table space how to solve ORA-00604 error" bar!

In the morning, I received a report from the development department that the database error was as follows:

Obviously, the error of "ORA-01653 unable to extend table" indicates that the SYSTEM tablespace cannot allocate new space, and that the AUD$ audit takes up most of the tablespace.

There are two basic reasons for ORA--01653:

1. Insufficient table space

Second, the failure to enable automatic growth leads to

Third, according to the report error, we can basically judge that the audit (AUD$) is too large, which leads to the lack of SYSTEM table space. SYSTEM table space mainly stores database dynamic views, base tables, data dictionaries and so on.

The investigation and solutions are as follows:

First, check the tablespace utilization and find that the SYSTEM tablespace utilization has reached 99.7%.

2. Whether to enable AUDIT audit function (Oracle 11g enables audit function by default)

SQL > show parameter audit (enabled if AUDIT_TRAIL has a value)

3. Check the size of the AUD$ base table and the amount of data (it is found that the AUD$ table occupies 9.3G of the SYSTEM table space)

= find out the large objects that occupy the top 10 of SYSTEM table space from dba_segments

Select * from (select SEGMENT_NAME,sum (bytes) / 1024 plus 1024 sx from dba_segments

Where tablespace_name='SYSTEM' group by segment_name)

Where sx > 100 order by sx desc

4. Clear the AUD$ table

SQL > truncate table SYS.AUD$

5. Check the tablespace usage again and find that the SYSTEM tablespace utilization rate is reduced to 7.7%.

= suggest other workarounds:

1. Oracle 11g enables AUDIT by default. For example, self-increasing SYSTEM tablespaces will continue to increase the use of space, so it is recommended to enable / disable AUDIT according to the actual business requirements, as follows.

SQL > alter system set audit_trail=none scope=spfile

Directly use the audit function in the spfile file to refer to audit_trail as none, that is, turn off the audit function.

After execution, restart the database.

View after startup:

SQL > show parameter audit_trail

2. Add data files

SQL > alter tablespace SYSTEM add datafile'/ * * size 100m autoextend on next 32m maxsize 1024m

Thank you for reading, the above is the content of "how to solve ORA-00604 errors in system tablespaces". After the study of this article, I believe you have a deeper understanding of how to solve the problem of ORA-00604 errors in system tablespaces, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.