这边维护的一个11.1.0.7 on HP11.31的双节点RAC库，alert日志最近每天都有GATHER_STATS_JOB的错误，开始以为是个临时用的表分析时没了，后来发现不是。日志如下：
Wed Jun 13 21:08:49 2012
WARNING: inbound connection timed out (ORA-3136)
Wed Jun 13 22:00:00 2012
Setting Resource Manager plan SCHEDULER[0x2C55]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Wed Jun 13 22:00:05 2012
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Wed Jun 13 22:14:43 2012
GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /oracle/app/diag/rdbms/kfdb/kfdb1/trace/kfdb1_j001_29798.trc:
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT" P01_1, insufficient privileges or does not exist
Wed Jun 13 22:17:59 2012
Thread 1 advanced to log sequence 1680 (LGWR switch)
Current log# 2 seq# 1680 mem# 0: /dev/dbdata1/rlvredo_121
Current log# 2 seq# 1680 mem# 1: /dev/dbdata1/rlvredo_122
Current log# 2 seq# 1680 mem# 2: /dev/dbdata2/rlvredo_221
Current log# 2 seq# 1680 mem# 3: /dev/dbdata2/rlvredo_222
Wed Jun 13 22:21:23 2012
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Thu Jun 14 02:00:00 2012
Clearing Resource Manager plan via parameter
Thu Jun 14 08:32:21 2012
Global Enqueue Services Deadlock detected. More info in file
/oracle/app/diag/rdbms/kfdb/kfdb1/trace/kfdb1_lmd0_4485.trc.
$ more /oracle/app/diag/rdbms/kfdb/kfdb1/trace/kfdb1_j001_29798.trc
Trace file /oracle/app/diag/rdbms/kfdb/kfdb1/trace/kfdb1_j001_29798.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/11.1.0/db_1
System name: HP-UX
Node name: kfora1
Release: B.11.31
Version: U
Machine: ia64
Instance name: kfdb1
Redo thread mounted by this instance: 1
Oracle process number: 224
Unix process pid: 29798, image: oracle@kfora1 (J001)
*** 2012-06-13 22:14:43.189
*** SESSION ID:(2165.65414) 2012-06-13 22:14:43.189
*** CLIENT ID:() 2012-06-13 22:14:43.189
*** SERVICE NAME:(SYS$USERS) 2012-06-13 22:14:43.189
*** MODULE NAME:(DBMS_SCHEDULER) 2012-06-13 22:14:43.189
*** ACTION NAME:(ORA$AT_OS_OPT_SY_6767) 2012-06-13 22:14:43.189
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT" P01_1, insufficient privileges or does not exist
*** 2012-06-13 22:14:43.195
GATHER_STATS_JOB: GATHER_TABLE_STATS('"ICD"','"TCHAT"','"P01_1"', ...)
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT" P01_1, insufficient privileges or does not exist
*** 2012-06-13 22:14:43.449
GATHER_STATS_JOB: GATHER_TABLE_STATS('"ICD"','"TCHAT"','"P01_2"', ...)
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT" P01_2, insufficient privileges or does not exist
*** 2012-06-13 22:14:43.691
GATHER_STATS_JOB: GATHER_TABLE_STATS('"ICD"','"TCHAT"','"P01_3"', ...)
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT" P01_3, insufficient privileges or does not exist
*** 2012-06-13 22:14:43.947
GATHER_STATS_JOB: GATHER_TABLE_STATS('"ICD"','"TCHAT"','"P02_1"', ...)
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT" P02_1, insufficient privileges or does not exist
*** 2012-06-13 22:14:44.200
GATHER_STATS_JOB: GATHER_TABLE_STATS('"ICD"','"TCHAT"','"P02_2"', ...)
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT" P02_2, insufficient privileges or does not exist
于是我手工对这个分区表做了一下分析，还是报错
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 14 12:05:52 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> desc icd.tchat
Name Null? Type
----------------------------------------- -------- ----------------------------
PARTID CHAR(4)
CCID NOT NULL NUMBER(9)
VDNID NOT NULL NUMBER(5)
CALLID NOT NULL VARCHAR2(25)
CHATID NOT NULL NUMBER(30)
SENDER VARCHAR2(50)
RECEIVER VARCHAR2(50)
SENDTIME DATE
CHATDATA CLOB
CLIENTFLAG VARCHAR2(50)
ISATTACH CHAR(1)
UVID VARCHAR2(100)
SENDERTYPE NUMBER(3)
RECEIVERTYPE NUMBER(3)
RESERVE1 VARCHAR2(50)
RESERVE2 VARCHAR2(50)
RESERVE3 VARCHAR2(50)
RESERVE4 VARCHAR2(50)
RESERVE5 VARCHAR2(50)
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ICD','TCHAT');
BEGIN DBMS_STATS.GATHER_TABLE_STATS('ICD','TCHAT'); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT", insufficient privileges or
does not exist
ORA-06512: at "SYS.DBMS_STATS", line 18397
ORA-06512: at "SYS.DBMS_STATS", line 18429
ORA-06512: at line 1
SQL> exec sys.dbms_stats.gather_table_stats ( ownname => 'ICD',tabname => 'TCHAT',partname => 'P01_1',granularity => 'DEFAULT',cascade => true,estimate_percent => 1, method_opt => 'FOR ALL COLUMNS SIZE 1');
BEGIN sys.dbms_stats.gather_table_stats ( ownname => 'ICD',tabname => 'TCHAT',partname => 'P01_1',granularity => 'DEFAULT',cascade => true,estimate_percent => 1, method_opt => 'FOR ALL COLUMNS SIZE 1'); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "ICD"."TCHAT" P01_1, insufficient privileges
or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 18397
ORA-06512: at "SYS.DBMS_STATS", line 18429
ORA-06512: at line 1
SQL> SQL>
请大家帮分析一下什么原因，谢谢！
附表结构