SYSTEM tablespace growing over 64GB

Question

My Oracle database version is 12.1.0.2.0

The DB I'm talking about is used for purpose of creating "packages" (not oracle package, but a package that contains codes/scripts) that are deployed in UAT/Production, etc environments. For this purpose, new schemas are created at runtime and old ones which are not used anymore are dropped periodically.

The tablespace in which these schemas are created/dropped is 84GB in size and there is over 50% free space available.

SYSTEM tablespace, which is of 64GB in size has only 10MB free.

All segments in SYSTEM tablespace are owned by either SYS or SYSTEM.

Top 15 rows of below query are:

SELECT owner, 
       segment_name,
       segment_type,
       bytes
  FROM dba_segments
 WHERE tablespace_name = 'SYSTEM'
 ORDER BY bytes desc;

output of above query

Does 64GB SYSTEM of tablespace sound right? Can it be reduced, and if yes then how?

Connor McDonald Connor McDonald · Accepted Answer · 2019-11-10T09:03:49

SOURCE$ is any PLSQL source code (procedures, packages, types, etc etc).

Similarly, IDL_UBL1, I_SOURCE are related to the same.

Your addenda means you have 116 million lines of PLSQL in that database. That's a fair bit of code :-)

If you really genuinely need 116 million lines of source code, then I don't think a 64G SYSTEM tablespace is unreasonable.

C_OBJ# being large suggests you potentially have a lot of objects in your database. So take a look at DBA_OBJECTS as well for opportunities to clean up.

SYSTEM tablespace growing over 64GB

1 Answers