SYSTEM Tablespace is Growing Abnormally in 12.2 Oracle database.

 My SYSTEM tablespace was growing rapidly. This was happening in my test environment. We observe that we were not doing much on this database.

Database version is 12cR2

Possibly there are few reasons for the same.

1)    Some has assigned default tablespace as SYSTEM
2)    Auditing is ON and consuming more space.
3)    SYS_LOB Objects uses lot of disk space
4)    Data dictionary objects uses lot of disk space

So now, how to find the root cause and what could be the solution?

TABLESPACE Status :- 

Let’s query to dba_segments table to find the root cause. I came across few scenarios in my different databases. Here I am discussing those scenarios, what could be the problem and solution of it.

Use below query to find out what are those segments which are consuming lot of space in SYSTEM tablespace.

Connect with sysdba.

[oracle@ora ~]$ sqlplus sys/sys@2023@PDB as sysdba

SQL :- select owner,segment_name,segment_type
 ,bytes/(1024*1024) size_mb
 from dba_segments
 where tablespace_name = 'SYSTEM'
 order by size_mb desc;

Here in above database my AUD$ table is growing rapidly and it is almost 30gb in size.

Problem
Auditing is enabled hence AUD$ table is growing very fast. Organization wants to do auditing and there is no option to disable it.

Solution
Few solutions which Oracle always recommended,
 ‘-  Move AUD$ to different tablespace
‘- Purge audit data
‘- Delete/truncate older data

Connect with sysdba.

[oracle@ora ~]$ sqlplus sys/sys@2023@PDB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 13 06:52:45 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

 

SQL> CREATE TABLE backup_AUD_13feb20 AS (SELECT * from AUD$);

 

Table created.

 

SQL> truncate table AUD$;

 

Table truncated.

 

SQL> commit;

 

Commit complete.

Recheck SYSTEM Tablespace status.