Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › ORA-20000: Unable to set values for column LOGIN_ID: ...
发新帖
saup007

133

积分

0

好友

17

主题

[SQL调优] ORA-20000: Unable to set values for column LOGIN_ID: does not exist or

1#
发表于 2012-12-29 09:03:44 | 查看: 12034| 回复: 4
通过查询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)


  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

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

  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)


  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

  24. ORA-20000: Unable to set values for column LOGIN_ID: does not exist or insufficient privileges

  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)                  

  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:

  13. Oracle Server - Enterprise Edition - Version 11.2.0.2 and later
  14. Information in this document applies to any platform.
  15. Symptoms

  17. Alert log shows the below error messages

  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



  27. Cause

  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.

  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.

  37. Solution

  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.

  44. References

  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"


  50. 相关内容


  53. 产品

  55. Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)

  57. 关键字

  59. COMPRESSION;GATHER_STATS_JOB

  61. 错误

  63. ORA-20000
复制代码
2#
发表于 2012-12-29 13:40:07
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的正常运行，也不会有其他副作用， 可以忽略

回复 只看该作者 道具 举报

3#
发表于 2013-1-22 11:40:18
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里呢。

回复 只看该作者 道具 举报

4#
发表于 2013-1-22 21:52:47
saup007 发表于 2013-1-22 11:40
HI:Maclean

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

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

回复 只看该作者 道具 举报

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

O了。thanks~！

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-27 02:58 , Processed in 0.061413 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569