Oracle database solves the problem that system table space is full

Time:2020-2-9

Sometimes you will find that the system tablespace of the database grows rapidly. Use the following statement to view the system tablespace usage. You can also use toad to look directly.

Select b.tablespace [u name "tablespace",
       b. Bytes / 1024 / 1024 "size M",
       (b.bytes - sum (NVL (a.bytes, 0)) / 1024 / 1024 "m used",
       Substr ((b.bytes - sum (NVL (a.bytes, 0)) / (b.bytes) * 100, 1, 5) "utilization"
  from dba_free_space a, dba_data_files b
 where a.file_id = b.file_id
   and b.tablespace_name = 'SYSTEM'
 group by b.tablespace_name, b.file_name, b.bytes
 order by b.tablespace_name;

Execute the following statement to see which object takes up the most

SELECT *
  FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
          FROM DBA_SEGMENTS
         WHERE TABLESPACE_NAME = 'SYSTEM'
         GROUP BY SEGMENT_NAME
         ORDER BY 2 DESC)
 WHERE ROWNUM < 10;

Generally, it is found that the audit table of AUD $takes up a large amount of resources.

Log in to the database directly and clean up the sys. AUD $table.

truncate table  SYS.AUD$;

 

 

 

If you want to turn off database auditing, please refer to the following link

https://www.cnblogs.com/chxmtl/p/11731085.html

http://blog.sina.com.cn/s/blog_b56640170102xbj7.html