Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONEOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db
System name: Linux
Node name: warehouse.99bill.com
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
Instance name: warehouse
Redo thread mounted by this instance: 1
Oracle process number: 100
Unix process pid: 9624, image: (J001)
*** 2013-07-27 18:12:16.894
*** SESSION ID:(160.14115) 2013-07-27 18:12:16.894
*** CLIENT ID:() 2013-07-27 18:12:16.894
*** SERVICE NAME:(SYS$USERS) 2013-07-27 18:12:16.894
*** MODULE NAME:(DBMS_SCHEDULER) 2013-07-27 18:12:16.894
*** ACTION NAME:(ORA$AT_OS_OPT_SY_11) 2013-07-27 18:12:16.894
ORA-20000: Unable to analyze TABLE "DW001"."I$_T_TXN_CTRL", insufficient privileges or does not exist
*** 2013-07-27 18:12:16.895
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"DW001"','"I$_T_TXN_CTRL"','""', ...)
DBMS_STATS: ORA-20000: Unable to analyze TABLE "DW001"."I$_T_TXN_CTRL", insufficient privileges or does not exist
可以确认是收集统计信息造成的错误。
通过查看回收站，可以找到相关被drop的table
这个错误不是重点，重点是这个时候为什么会收集统计信息
数据库自动收集统计信息的已经关闭。
11:41:49 sys@warehous>select t.program_name,t.schedule_name,t.schedule_type,t.enabled,t.state from dba_scheduler_jobs t where job_name='GATHER_STATS_JOB';
PROGRAM_NAME SCHEDULE_NAME SCHEDULE_TYP ENABL STATE
------------------------------ ---------------------------------------- ------------ ----- ---------------
GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP WINDOW_GROUP FALSE DISABLED
Elapsed: 00:00:00.00
11:41:54 sys@warehous>
我们设置的统计信息收集没有放在这个时间点。
从错误看，应该是个job造成的错误。
11:51:56 sys@warehous>select t.JOB,t.SCHEMA_USER,t.interval, what from dba_jobs t where broken='N' ;
JOB SCHEMA_USER INTERVAL WHAT
---------- -------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
211 OWF_MGR SYSDATE + (1440/(24*60)) FND_SVC_COMPONENT.EXECUTE_REQUEST (p_component_request_id => 1);
212 OWF_MGR sysdate + 4/24 declare errbuf varchar2(4000); retcode varchar2(4000); begin WF_BES_CLEANUP.CLEANUP_SUBSCRIBERS(errb
uf, retcode); end;
213 OWF_OWF SYSDATE + (1440/(24*60)) FND_SVC_COMPONENT.EXECUTE_REQUEST (p_component_request_id => 1);
165 SYS TRUNC(SYSDATE+1)+6/24 begin
--PRC_FREESPACE_MONITOR;
null;
end;
214 OWF_OWF sysdate + 4/24 declare errbuf varchar2(4000); retcode varchar2(4000); begin WF_BES_CLEANUP.CLEANUP_SUBSCRIBERS(errbuf, retcode); end;
386 OWB_OWNER sysdate + (6/1440) wb_rti_service_job.check_service(1);
409 SYSMAN sysdate + 1 / (24 * 60) EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
7 rows selected.
并没有发现有收集统计信息的job
？？？？？
但我们看看红色部分字体的内容，每分钟执行一次
11:58:14 sys@warehous>select count(*) from dba_tab_statistics where last_analyzed >sysdate-1;
COUNT(*)
----------
601
Elapsed: 00:00:00.10
在过去一天中有600多个object收集了统计信息。
之前发生错误的时候我查过，在发生错误的1小时内也有统计信息的收集。
我查了EMD_MAINTENANCE包相关信息：
Article-ID:
Note 285012.1
Circulation: UNDER_EDIT (EXTERNAL)
Folder: ST.EM.GridControl
Topic: Metrics
(Config,Collection,Baseline,Template,UDM)
Title:
Understanding the EMD_MAINTENANCE package from EM 10g Grid
Open-Remarks: See RemarksOn:NOTE:285012.1
Document-Type: BULLETIN
Impact: MEDIUM
Skill-Level: NOVICE
Updated-Date: 03-DEC-2004 14:07:38
References:
Authors: MRONCATI.US,
WGRUYTER.BE
Attachments: NONE
Content-Type: TEXT/X-HTML
Products: 1370;
Platforms: GENERIC;
Information in this article applies to:
Oracle Enterprise Manager Grid Control Release 1 (10.1.0.2.0, 10.1.0.3.0)
Goal:
To provide a better understanding of the sql package 'emd_maintenance' (also
called admin_maintenance_pkgbody.sql script)
The emd_maintenance is a package that ships with Enterprise Manager 10g Grid
Control. The script. is an administration script. that under normal
circumstances an administrator should not have to execute. However,
if the notifications become backlogged for some reason, you can execute this
script. as the sysman user and it will restart the DBMS jobs needed for the
notification subsystem.
Location of script.:
The file is located in the OMS's
$ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/admin/admin_maintenance_pkgbody.sql. This
same SQL is in the emd_maintanance package in the repository database.
Description of the package:
This maintanence package handles statistics maintenance for the EMD
Schema. It contains the following procedures:
update_stale_stats
This procedure collects stale statistics for the specified schema.
It collects it every Saturday at 2 am by default.
These parameters can be configured.
delete_all_stats
This procedure deletes all stats for the specified schema. It will
delete all schema statistics once on Saturday at 1am by default. These
parameters can be configured.
analyze_emd_schema
This procedure is called by update_stale_stats. It gathers
statistics for all objects in the schema. It runs only once.
It calls GATHER_STALE for objects that have monitoring turned on
and already have statistics or just analyzes objects that do not
have statistics.
pin_plsql
This procedure is called by update_stale_stats. This procedure will
pin all the critical EMD PL/SQL packages in memory. This is critical
to ensure that we do not fragment the shared pool.
Intended Usage of the Package:
If you want to stop and restart the jobs, you can do this using these routines:
- Stop
exec emd_maintenance.remove_em_dbms_jobs;
- Start/Restart:
exec emd_maintenance.submit_em_dbms_jobs;
Comments:
To see scheduled/completed jobs use the dba_jobs (or user_jobs) table.
To see running jobs use dba_jobs_running or user_jobs_running.
To see running jobs use dba_jobs_running or user_jobs_running.
To cancel a job use the above tables to find the job number ( job ) and then
call: dbms_jobs.remove_job(job)
@Reference:
@3092790: PERF: EXECUTE_EM_DBMS_JOB_PROCS RUNNING BEHIND SCHEDULE
@3323187: DBMS_JOB STUCK ON LIBCACHE LOCK AFTER ORA-4020 ERROR
脚本内容我还没有仔细研究，但这个任务和EM的定时操作相关。如果不使用EM，可以停用这个任务
我觉得这个报错也和这个job有关
这个应该是创建数据库时选择EM了的配置