oracle kup04040,ORA-20011: Approximate NDV failed: ORA-29913: 执行 ODCIEXTTABLEOPE

后台alert报以下信息：

DBMS_STATS: GATHER_STATS_JOB encountered errors.

Check the trace file.

Errors in file

/orahome/oracle/diag/rdbms/bims/bims/trace/bims_j000_11430.trc:

ORA-20011: Approximate NDV failed: ORA-29913: 执行 ODCIEXTTABLEOPEN

调出时出错

ORA-29400: 数据插件错误error opening file

/orahome/oracle/admin/bims/create/sh_sales_ext.log

关于这些错误，MOS文档ID

1274653.1有详细的说明，如下：

ORA-20011 ORA-29913

and ORA-29400 with Associated KUP-XXXXX Errors from

DBMS_STATS.GATHER_STATS_JOB (文档 ID 1274653.1)

类型:PROBLEM状态:PUBLISHED优先级:3

注释

(0)

APPLIES

TO

Oracle Server -

Standard Edition - Version 11.2.0.2 and later

Oracle Server -

Enterprise Edition - Version 11.2.0.2 and later

Oracle Server -

Personal Edition - Version 11.2.0.2 and later

Information in this

document applies to any platform.

SYMPTOMS

The key identifiers

in these cases are typically the following:

DBMS_STATS is being

executed in some form

ORA-20011, ORA-29913

and ORA-29400 Errors are seen, ORA-29913 specifying external

function "ODCIEXTTABLEOPEN" signifying that there was an error in

the callout by the external function "ODCIEXTTABLEOPEN " while

trying to open an external table.

Some specific

examples follow:

When attempting to

run GATHER_STATS_JOB the following errors

occur:

ORA-20011:

Approximate NDV failed:ORA-29913: error in executing

ODCIEXTTABLEOPEN callout

ORA-29400: data

cartridge error

usually accompanied

with varying other errors such as:

KUP-11024:

This external table can only be accessed from within a Data Pump

job

and

others.

Note: With cases

where the file was not found in the directory specified

raising:

KUP-04040: file %s in

%s not found

See:

Document 150737.1

ORA-29913, ORA-29400, KUP-04040 While Selecting from External

Table

Document 1290722.1

"KUP-04040: file timezdif.csv in TIMEZDIF_DIR not found " logged by

GATHER_STATS_JOB in alert.log after upgrade

Document 1305127.1

KUP-04040: file TRCA_CONTROL.txt in TRCA$STAGE not

found

Alternatively you may

get text in the trace file like:

***

2012-09-10 05:00:11.921

DBMS_STATS:

GATHER_STATS_JOB:

GATHER_TABLE_STATS('"SH"','"SALES_TRANSACTIONS_EXT"','""',

...)

DBMS_STATS:

ORA-20011: Approximate NDV failed: ORA-29913: error in executing

ODCIEXTTABLEOPEN callout

ORA-29400: data

cartridge error

error opening file

../demo/schema/log/ext_1v3.log

CAUSE

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.

SOLUTION

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

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

from

dba_objects

where object_name

like 'ET$%'

/

select owner,

TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE

from

dba_external_tables

order by

1,2

/

spool

off

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

system.&1 purge;

Enter value for 1:

ET$00654E1E0001

old 1: drop table

system.&1 purge

new 1: drop table

system.ET$00654E1E0001 purge

Other External

Table

With cases where

specific External tables (be they Demo Schema tables or other

tables) are missing, the process for handling them is much the same

and can be resolved by following the procedures below. For example,

if the additional error is 'error opening file

../demo/schema/log/ext_1v3.log', then this indicates that there is

a problem opening or locating the log file from the demo schema

directory. The implication is that the demo tables have not been

cleared up correctly:

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;

REFERENCES

NOTE:336014.1 - How

To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS

?

BUG:9387797 -

ORA-20011 / ORA-1870 EXECUTING GATHER_FIXED_OBJECTS_STATS AFTER

UPGRADE TO 11.2

@ BUG:9471411 - QUERY

ON X$KWQITCX RETURNS ORA-01870 WHEN

AQ_TM_PROCESSES=0

NOTE:150737.1 -

ORA-29913, ORA-29400, KUP-04040 While Selecting from External

Table

NOTE:1290722.1 -

"KUP-04040: file timezdif.csv in TIMEZDIF_DIR not found " logged by

GATHER_STATS_JOB in alert.log after upgrade

NOTE:1305127.1 -

KUP-04040: file TRCA_CONTROL.txt in TRCA$STAGE not

found