Applies to:

Oracle Application Object Library - Version: 11.5.10
This problem can occur on any platform.

Symptoms

After enabling Table Monitoring in Oracle Applications 11i according to:
Oracle Common Application Components Implementation Guide (B12394-02), running gather schema
statistics with (AUTO) option, causes it to hang with table (CS.DR$CS_KB_SETS_TL_N3$K) or orther DR$ tables.

Cause

The basic problem here is that the FND_STATS package makes a call to dbms_space.unused_space() for
the IOT in question. This raises an ORA-03211 which is caught by an exception handler and
processing then continues but we RETAIN the rowcache enqueue that we had acquired when trying to
invoke dbms_space.unused_space().
Later, the package then tries to execute the "alter table .. monitoring" command and this blocks
as it requires an exclusive rowcache entry.

The following justifies how the issue is related to this specific customer:
Gather Schema Statistics hangs causing ORA errors at the customer.

This is explained in the following bugs:
Bug 5564085: GATHERING SCHEMA STATS HANGS
Bug 5685189: GATHER AUTO MODE WAITING ON DR$IBE_CT_IMEDIA_SEARCH

Solution

1. Download and review the readme and pre-requisites for Patch 5685189.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Retest the issue.

5. Migrate the solution as appropriate to other environments.

Note: The above patch was released for 10g R2 database. If you can't find it for your database version then open an SR asking for a backport patch.

References

Bug 5685189 - GATHER AUTO MODE WAITING ON DR$IBE_CT_IMEDIA_SEARCH
Bug 5886183 - APPS:GATHER SCHEMA STATISTICS AUTO NOT EXCLUDE TABLES IN FND_EXCLUDE_TABLE_STATS

标题

在此添加评论
表情符号:

称呼

邮箱地址（可选）

个人主页（可选）

记住我？