SYSTEM Tablespace Using MSSM Is Abnormally Growing And The Clusters C_TOID_VERSION# and/ or C_OBJ#_INTCOL#.pdf

盖国强

552

1页

2次

2021-03-13

免费下载

SYSTEM Tablespace Using MSSM Is Abnormally Growing And The Clusters C_TOID_VERSION# and/ or C_OBJ#_INTCOL# Are Using Most

Of The Space. (Doc ID 943094.1)

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Oracle Database Cloud Exadata Service - Version N/A and later

Oracle Database Backup Service - Version N/A and later

Information in this document applies to any platform.

SYMPTOMS

You could notice that the SYSTEM tablespace is abnormally growing, you try to identify which segments are using most of the space, and you find that the cluster(s)

C_TOID_VERSION# and/ or C_OBJ#_INTCOL# is/ are using most of the space.

You can use the below SQL statement to find the top 10 sized segments of the SYSTEM Tablespace:

SQL> col segment_name format a20

SQL> col tablespace_name format a20

SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, MB from

(select segment_name, segment_type, tablespace_name,bytes/1024/1024 MB, rank() over(order by bytes desc) srank from dba_segments) where

srank <=10;

CAUSE

Bug 6390838 - C_OBJ#_INTCOL# KEEP GROWING WITH EXCHANGE PARTITIONS AND TTS IMPORT

This issue is with the cluster segments only seen in SYSTEM tablespace using MSSM (Manual Segment Space Management). Once space is allocated to a segment, it is not de-

allocated when rows are just deleted. It remains as a part of the segment, on the freelist. The free space is reused for the upcoming space needs. If there is a spike in the number

of new objects in the table, and the free space is not enough to accommodate it, then new space will be allocated. In index segments, if only one entry is left in an index block,

it will not be released back to the freelist, and if no other similar index keys are added, then the index will be very sparse. In the case of cluster, the space allocation is based on

the cluster key matches.

SOLUTION

1. Install Patch 6390838 if available for your DB version and platform.

2. Install 10.2.0.5 where the bug is fixed.

3. Upgrade to 11.2 where the bug doesn't exist.

If you can see that the segments increasing in the SYSTEM tablespace are not C_TOID_VERSION# and/ or C_OBJ#_INTCOL# , then you are not hitting this issue and please log

an SR with Oracle Support for diagnosis of the problem.

REFERENCES

BUG:5480675 - SYS.C_TOID_VERSION# CLUSTER IN SYSTEM TABLESPACE ABNORMALLY GROWING

BUG:6390838 - C_OBJ#_INTCOL# KEEP GROWING WITH EXCHANGE PARTITIONS AND TTS IMPORT

Didn't find what you are looking for?