EBS数据库自动收集统计报错ORA-00001: unique constraint (APPLSYS.FND...

yhj20041128001

操作系统:RHEL5.5 x86_64
数据库版本：10.2.0.4.0_64bit
FMS 应用版本：11.5.10


EBS发出的请求收集统计信息，报错：

+---------------------------------------------------------------------------+
应用对象程序库: Version : 11.5.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

FNDGSCST module: 统计数据收集模式
+---------------------------------------------------------------------------+

当前的系统时间为 13-12-2012 02:20:27

+---------------------------------------------------------------------------+

**Starts**13-12-2012 02:20:27
**Ends**13-12-2012 02:25:37
ORA-0000: normal, successful completion
+---------------------------------------------------------------------------+
FND_FILE 中日志消息开始
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= APPLSYS percent= 20 degree = 8 internal_flag= NOBACKUP
Unable to correctly update the history table - fnd_stats_hist.
-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated
Unable to correctly update the history table - fnd_stats_hist.
-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated
Unable to correctly update the history table - fnd_stats_hist.
-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated
Unable to correctly update the history table - fnd_stats_hist.
-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated
Unable to correctly update the history table - fnd_stats_hist.
-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated
Unable to correctly update the history table - fnd_stats_hist.
-1 - ORA-00001: unique constraint (APPLSYS.FND_STATS_HIST_U1) violated
Unable to correctly update the history table - fnd_stats_hist.

Maclean Liu(刘相兵

请直接参考metalink文档 ：　Gather Schema Stats Fail With ORA-0001 "unique constraint (APPLSYS.FND_STATS_HIST_U1) violated" Errors [ID 470556.1]

1. Changes
   
2. 
   
3. Version of FND_STATS package changed
   
4. Cause
   
5. 
   
6. Update statement against FND_STATS_HIST table is failing due to unique constraint on request_id
   
7. column.
   
8. 
   
9. Error message is same as described in the following bug:
   
10. 
    
11. BUG 6264433 - ORA-1 ERRORS ARE REPORTED FOR APPLSYS.FND_STATS_HIST_U1 DURING GATHERING STATS
    
12. 
    
13. After gathering statistics on the tables, history information will be stored in fnd_stats_hist
    
14. table. But this table requires a unique value on request_id column. This is forced by the use of
    
15. unique constraint on the table.
    
16. 
    
17. 
    
18. 
    
19. Solution
    
20. 
    
21. To implement the solution, please execute the following steps:
    
22. 
    
23. 1. Take a backup of the FND_STATS_HIST table.
    
24. 2. Truncate table FND_STATS_HIST:
    
25. 
    
26. TRUNCATE TABLE APPLSYS.FND_STATS_HIST;
    
27. 
    
28. 
    
29. 3. Re-submit Gather Schema Statistics program.
    
30. 
    
31. NOTE: One does NOT need to truncate this table after each run of Gather Schema Statistics. Doing it once will be enough, because each run of GSS will use a new request_id.  The FND_STATS_HIST table stores old statistics that can be restored in case new statistics are not acceptable. Deleting data from this table should not cause any functional problems.
    
32. 
    

复制代码

yhj20041128001

Dear Liu，我按照你给出的方法做，现在在生产环境没有再出现类似的告警了。非常感谢你的回复。谢谢！