Thursday, March 31, 2016

SYSTEM Tablespace Very Large.

In my experience SYSTEM Tablespace are very large due to the lots of source code and audit information. You can see large table in system tablespace are SOURCE$, AUD$, IDL_UB1$ and so on.

Never truncate SOURCE$, IDL_* (IDL_UB1$,IDL_CHAR$,IDL_UB2$,IDL_SB4$) Tables. Because those tables containing PL-SQL Source code. 

You can truncate AUD$ Table if you don't required to contain large amount of audit information.

You can find out the segment list using this Query :

SELECT OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES / (1024 * 1024) SIZE_MB
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'SYSTEM' AND BYTES / (1024 * 1024) > 1
ORDER BY SIZE_MB DESC; 

No comments:

Post a Comment