在alter日志中发现“SYS_AUTO_SQL_TUNING_TASK”任务执行失败，日志信息如下：

Thu Jun 23 22:00:00 2016
Setting Resource Manager plan SCHEDULER[0x32DC]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Thu Jun 23 22:00:00 2016
Starting background process VKRM
Thu Jun 23 22:00:00 2016
VKRM started with pid=105, OS id=26427
Thu Jun 23 22:00:01 2016
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Thu Jun 23 22:00:02 2016
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /oracle/database/diag/rdbms/indsdb/indsdb/trace/indsdb_j000_26423.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.
Thu Jun 23 22:01:43 2016
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Fri Jun 24 02:00:00 2016
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter

检查提示的trc文件，信息如下：

Trace file /oracle/database/diag/rdbms/indsdb/indsdb/trace/indsdb_j000_26423.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/database/product/11.2.0.4/db_1
System name: Linux
Node name: indsdb
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine: x86_64
Instance name: indsdb
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 26423, image: oracle@indsdb (J000)

*** 2016-06-23 22:00:02.661
*** SESSION ID:(1048.8055) 2016-06-23 22:00:02.661
*** CLIENT ID:() 2016-06-23 22:00:02.661
*** SERVICE NAME:(SYS$USERS) 2016-06-23 22:00:02.661
*** MODULE NAME:(DBMS_SCHEDULER) 2016-06-23 22:00:02.661
*** ACTION NAME:(ORA$AT_OS_OPT_SY_907) 2016-06-23 22:00:02.661

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.

*** 2016-06-23 22:00:02.662
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"MTS"','"ET$05F200570001"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.

*** 2016-06-23 22:00:02.671
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"MTS"','"ET$05F400030001"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.

可以发现，是数据库在每天运行DBMS_STATS.GATHER_STATS_JOB：GATHER_TABLE_STATS是对 schema MTS 的“ET$05F200570001”和“ET$05F400030001” 失败，提示是external table 无法被 Data Pump job 访问。

参考该博客解决遇到的错误： http://blog.itpub.net/27042095/viewspace-1150672/

产生问题的原因：产生该问题的主要原因是对应的外部已经不存在，但是数据库的数据字典信息依旧记录这该外部表，对应的信息未做更新；当DBMS_STATS执行时，数据库不能访问到该外部表因为外部表已经不存在（详细信息见MOS给出的解释）。

The primary cause of this issue is that an external table existed at some point in time but does not now. However, the database still believes the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.

There are many reasons that an external table may not exist including:

Temporary Datapump external tables have not been cleaned up properly. The dictionary information should have been dropped when the DataPump jobs completed.

An External table has been removed without clearing up the corresponding data dictionary information. For example: Oracle Demo Schema Tables such as the external table “SALES_TRANSACTIONS_EXT” may have been removed but the dictionary has not been updated to reflect this. The "SALES_TRANSACTIONS_EXT" table is an external table in the "SH" schema which is one of Demo Schema provided by Oracle.

解决方法：根本方法就是清除数据字典中这种孤立的信息，即删除外部表定义。当然，还需要具体问题具体分析其主要原因。

Essentially the solution to this issue is to clean up the orphaned dictionary entries. Different solutions are appropriate dependent on the exact nature of the primary cause and these are outlined below:

Temporary Datapump External Table

Ensure that there are no DataPump jobs running at the same time as the DBMS_STATS job (this is to avoid any potential complications associated with cleaning up at the same time as someting else is running).

Check and clean up orphaned DataPump jobs. Both above steps can be done by following note:

Note:336014.1 How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?

Identify external tables. To do this, Run the following as SYSDBA in SQL*Plus

确定外部表信息：

spool obj.out

set linesize 200 trimspool on

set pagesize 2000

col owner form a30

col created form a25

col last_ddl_time form a25

col object_name form a30

col object_type form a25

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE, status, to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
2 from dba_objects where object_name like 'ET$%' /

OWNER    OBJECT_NAME OBJECT_TYPE STATUS   CREATED LAST_DDL_TIME
---------------- ------------------- ------------------ ------------ ------------------ --------------------
MTS ET$05F400030001   TABLE VALID   05-1月 -2016 10:09:25 05-1月 -2016 10:09:25
MTS ET$05F200570001   TABBLE VALID   05-1月 -2016 10:06:06 05-1月 -2016 10:06:06

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE

from dba_external_tables

order by 1,2

/

OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_TYPE
------------------------------ ------------------------------ ------------------------------ -----------
MTS ET$05F200570001 DPDATA CLOB
MTS ET$05F400030001 DPDATA CLOB
SH SALES_TRANSACTIONS_EXT DATA_FILE_DIR CLOB

Correlate the information from DBA_OBJECTS and DBA_EXTERNAL TABLES above to identify the temporary external tables that belong to the DataPump.

Drop the temporary external tables that belong to the DataPump. eg:

SQL> drop table MTS.&1 purge;

Enter value for 1: ET$

05F200570001

old 1: drop table system.&1 purge

new 1: drop table system.ET$

05F200570001

purge

Other External Table

SQL> drop table MTS.&1 purge;

Enter value for 1: ET$

05F400030001

old 1: drop table system.&1 purge

new 1: drop table system.ET$

05F400030001

purge

Other External Table

Try to locate the files for these tables in their directory. It may be that the files still exist but they have just been renamed or re-located. If that is the case you can correct the location to avoid the problem. If the file has been removed then follow either the following steps:

Lock the statistics on these tables by using the following command:

DBMS_STATS.LOCK_TABLE_STATS ('ownname','tabname');

(This step prevents DBMS_STATS from gathering against the missing table)

AND/OR

Remove the table. For example if the "SALES_TRANSACTIONS_EXT" Demo table is missing you would use :

DROP TABLE SALES_TRANSACTIONS_EXT;

ITPub博客

ORA-20011 ORA-29913 Errors from DBMS_STATS.GATHER_STATS_JOB

最新文章