Untitled
SYSTEM Tablespace is space increasing Abnormally in 12c
SYSTEM tablespace was growing rapidly. We observe that we were not doing much on this database. Only few users were working to test the application
There was no much load as well, still it’s went up to 100gb.
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
How to find the root cause and what could be the solution?
Let’s query to dba_segments table to find the root cause. Here we have few scenarios in different databases, 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
Scenario 2
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.
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.
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′,'<USERNAME>’);
Scenario 4
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.
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.