Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Neo-bDec 12 2012 — edited Dec 12 2012

Hello All,

I am using Oracle 11.2.0.3

in my alert logs I found the below errors:

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.

to resolve that i did the below steps:

SELECT owner_name, job_name, operation, job_mode, 
state, attached_sessions 
FROM dba_datapump_jobs 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2;

the result was that there is two running jobs and the attached_session flag was equal to 1, something like the below:

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT      SYS_IMPORT_TABLE_05      IMPORT TABLE RUNNING        1
SCOTT      SYS_IMPORT_TABLE_01 IMPORT TABLE     RUNNING        1

so i stopped these 2 jobs using the below statements:

SET serveroutput on 
SET lines 100 
DECLARE 
   h1 NUMBER; 
BEGIN 
   h1 := DBMS_DATAPUMP.ATTACH('SYS_IMPORT_TABLE_05','SCOTT'); 
   DBMS_DATAPUMP.STOP_JOB (h1); 
END; 
/
SET serveroutput on 
SET lines 100 
DECLARE 
   h1 NUMBER; 
BEGIN 
   h1 := DBMS_DATAPUMP.ATTACH('SYS_IMPORT_TABLE_01','SCOTT'); 
   DBMS_DATAPUMP.STOP_JOB (h1); 
END; 
/

now the result become like below as stop pending in the state column:

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT      SYS_IMPORT_TABLE_05      IMPORT TABLE STOP PENDING 1
SCOTT      SYS_IMPORT_TABLE_01 IMPORT TABLE     STOP PENDING 1

for that I did more research and i dropped two temporary external tables after I got their names using the below query:

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$%'
/

but nothing changed in the output of the below query, the state stayed STOP PENDING

SELECT owner_name, job_name, operation, job_mode, 
state, attached_sessions 
FROM dba_datapump_jobs 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2;

for that I used stop job but with the immediate flag as 1

 DBMS_DATAPUMP.STOP_JOB (h1,1);

now the result of the query is NOT RUNNING in the state column:

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT      SYS_IMPORT_TABLE_05      IMPORT TABLE     NOT RUNNING   0
SCOTT      SYS_IMPORT_TABLE_01      IMPORT TABLE     NOT RUNNING   0

and I discovered that one of my import jobs that I did not realize that it was running, failed due to a fatal error, it is the one related to the import of one table.

My question:

Now the below query should return zero records no ?
How i can clear the result ? and does it affect any future import jobs for the same table?

SELECT owner_name, job_name, operation, job_mode, 
state, attached_sessions 
FROM dba_datapump_jobs 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2;

290833

How about something like this?

  1  with t as (
  2     select rownum as rn1
  3     , row_number() over (order by rownum desc) rn2
  4     , trunc(count(*) over () / 2) as mid
  5     from all_objects where rownum <= 6
  6  )
  7  select rn1 as leftside
  8  , rn2 as rightside
  9  from t
 10  where rn1 <= mid
 11* order by rn1
SQL> /

  LEFTSIDE  RIGHTSIDE
---------- ----------
         1          6
         2          5
         3          4

cheers,
Anthony

121256

with t as ( select level*101 as x from dual connect by level <= 7),
    tt as ( select x, row_number() over (order by x) - 1 as rn, ceil(count(*) over () / 2) as win from t)
select min(decode(trunc(rn/win), 0, x)) as x1,
       min(decode(trunc(rn/win), 1, x)) as x2
  from tt
  group by mod(rn, win)
  order by mod(rn, win)
;

           X1            X2
------------- -------------
          101           505
          202           606
          303           707
          404

Sven W.

It seems to me as if you try to force a client issue (layout thingie) onto the database server.

The displaying of select results should be handled by the client. Most client tools have enough capabilities to do such a thing. It is generally a bad idea to do the whole layouting in the select already.

Split the result set and print the second part in reversed order.

btw: Same idea goes for stuff like pivot querys.

450441

Currently your MOD(t1.rn, ...) = MOD(t2.rn,...)

is what is giving you

1 4
2 5
3 6

because that's what you asked me for when I supplied this query in your previous thread.

Now MOD is the wrong approach. You want

1 COUNT
2 COUNT-1
3 COUNT-2

So replace the MOD comparison with

AND t2.rn(+) = (SELECT count(1) from member) - ( t1.rn - 1 )
ORDER BY t1.rn

The outer join is so that if there are an odd number of rows, t1 value will be present and t2 value will be null for the last row.

Message was edited by:
Dave Hemming

Using aketi's test table query would be

with t as
(SELECT val as nom, row_number() over (order by val ASC) as rn from testT)
select t1.nom as leftside, decode(t2.nom,t1.nom,null,t2.nom) as rightside
from t t1, t t2
where rownum < (SELECT COUNT(val) / 2 + 1 AS table_length FROM testt) 
AND t2.rn(+) = (SELECT count(1) from testt) - ( t1.rn - 1 )

Aketi Jyuuzou

This is an interesting question.

create table testT(Val) as
select RowNum*10 from all_catalog
 where RowNum <=7;

select Left,Right
from (select Rn,Cnt,Val as Left,
      case when Rn <= Cnt/2
           then Lead(Val,abs(Cnt-2*Rn+1)) over(order by Val) end as Right
      from (select Val,
            Row_Number() over(order by Val) as Rn,
            count(*) over() as Cnt
            from testT))
where Rn <= ceil(Cnt/2);

LEFT  RIGHT
----  -----
  10     70
  20     60
  30     50
  40   null

121256

Oops :( Misread

with t as ( select level*101 as x from dual connect by level <= 7),
    tt as ( select x, row_number() over (order by x) - 1 as rn, (count(*) over () - 1) / 2 as median_rn from t)
select min(case when rn <= median_rn then x end) as x1,
       min(case when rn >  median_rn then x end) as x2
  from tt
  group by abs(rn - median_rn)
  order by abs(rn - median_rn) desc
;

           X1            X2
------------- -------------
          101           707
          202           606
          303           505
          404

121256

>   group by abs(rn - median_rn)

Another variation of odd count:

  group by abs(trunc(median_rn - rn + 0.1))

           X1            X2
------------- -------------
          101
          202           707
          303           606
          404           505

1 - 7

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 9 2013

Added on Dec 12 2012

#general-database-discussions

4 comments

5,178 views

Oracle Database Discussions

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

Comments

Post Details