SYSTEM Tablespace is Growing Abnormally in 12c
My SYSTEM tablespace was growing rapidly. This was happening in my test environment. We observe that we
There was no much load as well, still it’s went up to 100gb. This seems to be some wired behavior .
Database version is 12cR1
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?
Let’s query to dba_segments table to find the root cause. I came across few scenarios in my different databases.
Use below query to find out what are those segments which are consuming lot of space in SYSTEM tablespace.
select owner,segment_name,segment_type
,bytes/(1024*1024) size_mb
from dba_segments
where tablespace_name = ‘SYSTEM’
order by size_mb desc
Scenario 1
Problem
Auditing is enabled hence AUD$ table is growing very fast. Organization wants to do auditing and there is
Solution
Few solutions which Oracle always recommended,
‘- Move AUD$ to different tablespace
‘- Purge audit data
‘- Delete/truncate older data
Visit https://support.oracle.com there are many notes available for this issue..
Scenario 2
Check below screenshot,
These tables are core dictionary tables. Consider like these tables stores your database/schema/table procedure’s
You cannot move those tables to another table space or truncate or reorg of these table. Oracle strictly says that
So, is this the ideal scenario?
Can’t we control the growth of those tables like AUD$ tables?
Answer is, Yes, this is the ideal scenario and we don’t have control over it.
Scenario 3
SYSTEM tablespace growing unexpectedly after database upgrade OR after IMPDP
If you are upgrading your database from 11g to 12c, this might happen that you observer some unexpected
Here I would like to highlight that when you do the data import; import do lots of compilation of your function,
This behavior found in 12c database due to new features introduced and reported as BUG. Here is the bug
Bug 5910872 : ARGUMENT$ DATA UNNECESSARILY DUPLICATED.
Here the workaround is to recompile the objects. This will decrease the logical space, not the physical space.
Run this command after impdp,
Example
1) alter session set events =’10946 trace name context forever, level 8454144′;
2) exec utl_recomp.recomp_parallel(‘4′,’SCOTT’);
Scenario 4
Many a times it has been observed that data dictionary tables are occupying more space like BOOTSTRAP$,
Such issue found in Oracle Database 9i, 10g, 11g. Still I haven’t faced this type of issue in 12c or 18c.
To resolve this issue run catalog.sql and catproc.sql and re-query to dba_segments.