ORA-20000: Unable to set values for column LOGIN_ID: does not exist or

saup007

通过查询MOS，找到一篇适合此例的文章，但总觉得差那么一点点，还请Maclean看一下，谢谢。

我在二个库遇到同样的问题，报错先后排序

A：模拟库

linux 5.5 x86

11.2.0.2 无psu apply

alter日志如下：

1. Sat Dec 22 10:06:34 2012
   
2. DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
   
3. Errors in file /u01/app/oracle/diag/rdbms/yongbing/YONGBING/trace/YONGBING_j000_2012.trc:
   
4. ORA-20000: Unable to set values for table DBMS_TABCOMP_TEMP_UNCMP: does not exist or insufficient privileges

复制代码

trace文件如下：

1. cat /u01/app/oracle/diag/rdbms/yongbing/YONGBING/trace/YONGBING_j000_2012.trc
   
2. Trace file /u01/app/oracle/diag/rdbms/yongbing/YONGBING/trace/YONGBING_j000_2012.trc
   
3. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
   
4. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
5. ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
   
6. System name:     Linux
   
7. Node name:     comp209
   
8. Release:     2.6.18-194.el5PAE
   
9. Version:     #1 SMP Tue Mar 16 22:00:21 EDT 2010
   
10. Machine:     i686
    
11. Instance name: YONGBING
    
12. Redo thread mounted by this instance: 1
    
13. Oracle process number: 43
    
14. Unix process pid: 2012, image: oracle@comp209 (J000)
    
15. 
    
16. 
    
17. *** 2012-12-22 10:06:34.112
    
18. *** SESSION ID:(616.44285) 2012-12-22 10:06:34.112
    
19. *** CLIENT ID:() 2012-12-22 10:06:34.112
    
20. *** SERVICE NAME:(SYS$USERS) 2012-12-22 10:06:34.112
    
21. *** MODULE NAME:(DBMS_SCHEDULER) 2012-12-22 10:06:34.112
    
22. *** ACTION NAME:(ORA$AT_OS_OPT_SY_3286) 2012-12-22 10:06:34.112
    
23. 
    
24. ORA-20000: Unable to set values for table DBMS_TABCOMP_TEMP_UNCMP: does not exist or insufficient privileges
    
25. 
    
26. *** 2012-12-22 10:06:34.112
    
27. DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"ERATING_YBTX"','"DBMS_TABCOMP_TEMP_UNCMP"','""', ...)
    
28. DBMS_STATS: ORA-20000: Unable to set values for table DBMS_TABCOMP_TEMP_UNCMP: does not exist or insufficient privileges

复制代码

B：线上库

linux 5.6 x86_64

11.2.0.2.3  打过psu

alter日志：

1. Sat Dec 29 06:00:12 2012
   
2. DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
   
3. Errors in file /u01/app/oracle/diag/rdbms/daxiao/DAXIAO/trace/DAXIAO_j003_28641.trc:
   
4. ORA-20000: Unable to set values for column LOGIN_ID: does not exist or insufficient privileges

复制代码

trace文件如下：

1. cat /u01/app/oracle/diag/rdbms/daxiao/DAXIAO/trace/DAXIAO_j003_28641.trc
   
2. Trace file /u01/app/oracle/diag/rdbms/daxiao/DAXIAO/trace/DAXIAO_j003_28641.trc
   
3. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
   
4. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
5. ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
   
6. System name:        Linux
   
7. Node name:        dbserver8
   
8. Release:        2.6.18-274.17.1.el5
   
9. Version:        #1 SMP Tue Jan 10 17:25:58 EST 2012
   
10. Machine:        x86_64
    
11. Instance name: DAXIAO
    
12. Redo thread mounted by this instance: 1
    
13. Oracle process number: 74
    
14. Unix process pid: 28641, image: oracle@dbserver8 (J003)
    
15. 
    
16. 
    
17. *** 2012-12-29 06:00:12.092
    
18. *** SESSION ID:(576.31881) 2012-12-29 06:00:12.092
    
19. *** CLIENT ID:() 2012-12-29 06:00:12.092
    
20. *** SERVICE NAME:(SYS$USERS) 2012-12-29 06:00:12.092
    
21. *** MODULE NAME:(DBMS_SCHEDULER) 2012-12-29 06:00:12.092
    
22. *** ACTION NAME:(ORA$AT_OS_OPT_SY_1854) 2012-12-29 06:00:12.092
    
23. 
    
24. ORA-20000: Unable to set values for column LOGIN_ID: does not exist or insufficient privileges
    
25. 
    
26. *** 2012-12-29 06:00:12.092
    
27. DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"ERATING_HSSG"','"DBMS_TABCOMP_TEMP_UNCMP"','""', ...)
    
28. DBMS_STATS: ORA-20000: Unable to set values for column LOGIN_ID: does not exist or insufficient privileges

复制代码

分析如下：

因为临时表的创建，刚好被录入晚上22点收集统计信息列表，但还没收集表DBMS_TABCOMP_TEMP_UNCMP时，它已经被删除了，此时DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS就会报错了。

疑问？我怎么找到DBMS_TABCOMP_TEMP_UNCMP曾经在gather_stats_job的收集的列表里呢？在11.2.0.2.3中报clumn LOGIN_ID不存在，而不是表了，呵呵！

平常做试验dba_tab_modifications，dml 10%会在gather_stats_job的收集列表里，dba_tab_modifications基表是sys.MON_MODS_ALL$，但DBMS_TABCOMP_TEMP_UNCMP已经被删除了，还可以查吗？或者怎么知道历史gather_stats_job收集列表里？

这个sql，是没有结果的。

select obj# from sys.MON_MODS_ALL$ where timestamp > = to_date('20121228 22:00:00','yyyymmdd hh24:mi:ss')
minus
select object_id from dba_objects

--DBMS_TABCOMP_TEMP_UNCMP 表是干嘛用的 -- 11.2新特性，压缩advisory的中间临时表

two tables (DBMS_TABCOMP_TEMP_UNCMP & DBMS_TABCOMP_TEMP_CMP) could becreated because of a 11.2 new feature, Compression Advisory.

These two temporary tables  are created during the execution of package -DBMS_COMPRESSION, while doing the analyze of a table. They are created underthe same schema name.

These tables are used to determine the level of compression level can beachieved.

--下面参考How Does Compression Advisor Work? [ID 1284972.1]

DBMS_TABCOMP_TEMP_UNCMP is created with 99% sample blocks by default. DBMS_TABCOMP_TEMP_CMP is created with compress option based on DBMS_TABCOMP_TEMP_UNCMP. COMPRESSION_RATIO is generated by comparing DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP. These two tables will be dropped immediately after the estimation.


参考MOS：How Does Compression Advisor Work? [ID 1284972.1]
知道11.2新特性的bug

NOTE:1463793.1 - ORA-20000: Unable to set values for table DBMS_TABCOMP_TEMP_UNCMP: does not exist or insufficient privileges

1.      ORA-20000: Unable to set values for table DBMS_TABCOMP_TEMP_UNCMP: does not exist or insufficient privileges [ID 1463793.1]     转到底部   
   
2. 修改时间:2012-6-1类型:PROBLEM状态:MODERATED优先级:3   
   
3. 注释 (0)                  
   
4. 
   
5. In this Document
   
6. Symptoms
   
7. Cause
   
8. Solution
   
9. References
   
10. This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
    
11. Applies to:
    
12. 
    
13. Oracle Server - Enterprise Edition - Version 11.2.0.2 and later
    
14. Information in this document applies to any platform.
    
15. Symptoms
    
16. 
    
17. Alert log shows the below error messages
    
18. 
    
19. Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
    
20. 2012-05-24 22:00:13.107000 +02:00
    
21. DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
    
22. Errors in file /data01/oracle/diag/rdbms/octoprd/OCTOPRD2/trace/OCTOPRD2_j002_2772.trc:
    
23. ORA-20000: Unable to set values for table DBMS_TABCOMP_TEMP_UNCMP: does not exist or insufficient privileges
    
24. 
    
25. 
    
26. 
    
27. Cause
    
28. 
    
29. This issue matches Bug 13080933 which is a duplicate of Bug 9939773.
    
30. Bug 9939773 has been identified as not a bug with the below commands.
    
31. 
    
32. The compression advisor creates intermediate tables (creates/drops tables DBMS_TABCOMP_TEMP_UNCMP),
    
33. In case a GATHER_STATS_JOB is run at the same time , the table(s) will be in the list of tables to be analyzed.
    
34. In case the table is automatically dropped by the compression advisory job, before the GATHER_STATS_JOB has reached to that table in the list, the ORA-2000 will be hit.
    
35. When DBMS_STATS finds it is no longer available it writes to the alert log.This is no different from the case where a user table is dropped during a statistics gather.
    
36. 
    
37. Solution
    
38. 
    
39. You can ignore this error as
    
40. - this is not critical error.
    
41. - this will not result in termination of the gather stats job for the rest of the tables. Other objects won't be affected by this error and gather stats job continues safely on other user objects.
    
42. - the message in the alert log has only informative character.
    
43. 
    
44. References
    
45. 
    
46. NOTE:1247864.1 - DataPump Export Raises ORA-31693 ORA-02354 ORA-942 On DBMS_TABCOMP_TEMP_CMP And DBMS_TABCOMP_TEMP_UNCMP
    
47. @ BUG:9939773 - ORA-20000: UNABLE TO ANALYZE TABLE "DOCSADM"."DBMS_TABCOMP_TEMP_UNCMP"
    
48. 
    
49. 
    
50. 相关内容
    
51. 
    
52. 
    
53. 产品
    
54. 
    
55. Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
    
56. 
    
57. 关键字
    
58. 
    
59. COMPRESSION;GATHER_STATS_JOB
    
60. 
    
61. 错误
    
62. 
    
63. ORA-20000

复制代码

Maclean Liu(刘相兵

The compression advisor creates intermediate tables (creates/drops tables DBMS_TABCOMP_TEMP_UNCMP),
In case a GATHER_STATS_JOB is run at the same time , the table(s) will be in the list of tables to be analyzed.
In case the table is automatically dropped by the compression advisory job, before the GATHER_STATS_JOB has reached to that table in the list, the ORA-2000 will be hit.
When DBMS_STATS finds it is no longer available it writes to the alert log.This is no different from the case where a user table is dropped during a statistics gather.

Solution

You can ignore this error as
- this is not critical error.


就这个解释来看是 compression advisor  自己建的临时表 被gather stats job 作为收集对象，但是这些临时对象旋即被compression advisory job, 删除


Oracle Support认为这既不会影响gather stats job的正常运行，也不会有其他副作用， 可以忽略

saup007

Maclean Liu(刘相兵 发表于 2012-12-29 13:40
The compression advisor creates intermediate tables (creates/drops tables DBMS_TABCOMP_TEMP_UNCMP),
...

HI:Maclean

我又看了这篇文章：http://www.askmaclean.com/archives/smon-flush-dml-statistics-mon-mods.html

我怎么找到DBMS_TABCOMP_TEMP_UNCMP曾经在gather_stats_job的收集的列表里呢？

或者曾经存过在mon_mods$、mon_mods_all$、dba_tab_modifications里呢。

Maclean Liu(刘相兵

saup007 发表于 2013-1-22 11:40
HI:Maclean

我又看了这篇文章：http://www.askmaclean.com/archives/smon-flush-dml-statistics-mon-mod ...

闪回 、或者日志挖掘。。。12c有个功能可以清晰看到这个列表

saup007

Maclean Liu(刘相兵 发表于 2013-1-22 21:52
闪回 、或者日志挖掘。。。12c有个功能可以清晰看到这个列表

O了。thanks~！