SYSTEM Tablespace is Growing Abnormally in 12c

SYSTEM Tablespace is Growing Abnormally in 12c

My SYSTEM tablespace was growing rapidly. This was happening in my test environment. We observe that we were not doing much on this database. Only few users were working to test the application and doing approx 40-50 transaction per day. That's it.
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

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?

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.
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

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

Visit https://support.oracle.com  there are many notes available for this issue..

Scenario 2
Check below screenshot,

Here in 2nd scenario, ARGUMENT$, I_ARGUMENT1, I_ARGUMENT2, IDL_UB1$, SOURCE$ ; tables and indexes showing very huge in size.
These tables are core dictionary tables. Consider like these tables stores your database/schema/table procedure’s meta data information.

You cannot move those tables to another table space or truncate or reorg of these table. Oracle strictly says that don’t touch those tables as these are metadata tables. If you move or truncate tables, you might lose database information and this will corrupts your database as well.

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 growth of SYSTEM tablespace.

Here I would like to highlight that when you do the data import; import do lots of compilation of your function, procedure and packages. Metadata is associated with PL/SQL library. During compilation it generates duplicate rows in ARGUMENT$ table, which cause to huge size of ARGUMENT$ table.

This behavior found in 12c database due to new features introduced and reported as BUG. Here is the bug information.
Bug 5910872 : ARGUMENT$ DATA UNNECESSARILY DUPLICATED.

Solution

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$, FILE$, UNDO$, IDL_UB1$, SOURCE$ etc.
Such issue found in Oracle Database 9i, 10g, 11g. Still I haven’t faced this type of issue in 12c or 18c.

Solution
To resolve this issue run catalog.sql and catproc.sql and re-query to dba_segments. This will work only in certain scenarios only, this is not for all.


Hope this note will help to understand the causes and solutions available for the same.



Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert