0 Replies Latest reply on Jul 6, 2016 8:35 AM by kulsam

    ORA-29913: error in executing ODCIEXTTABLEOPEN callout + ORA-29400: data cartridge error + KUP-11010: unable to open at least one dump file for fetch

    kulsam

      Ours is a RAC environment. We have 2 nodes. We created a Directory and gave Read/Write Permission to the newly created directory.

       

      CREATE OR REPLACE DIRECTORY DB_ETL_DIR AS '/nas/etl_cn_data/ETL/data';

      GRATN READ, WRITE ON DIRECTORY DB_ETL_DIR TO DB_ETL_USER

       

      Created EXternal Table -

       

      CREATE TABLE DB_ETL_USER.EXT_ABC

      (

        COLUMN_1                    VARCHAR2(34 CHAR),

        COLUMN_2                CHAR(3 CHAR),

        COLUMN_3                 NUMBER(1),

        COLUMN_4                  DATE,

        COLUMN_5               NUMBER(1),

      )

      ORGANIZATION EXTERNAL

        (  TYPE ORACLE_DATAPUMP

           DEFAULT DIRECTORY DB_ETL_DIR

           ACCESS PARAMETERS

             ( NOLOGFILE  )

           LOCATION (DB_ETL_DIR:'EXT_ABC.dmp')

        )

      REJECT LIMIT 0;

       

      We try to access the external table using below code -

       

      DECLARE
      v_file BFILE := BFILENAME ('DB_ETL_DIR', 'EXT_ABC.dmp');
      l_dummy varchar2(10);
      BEGIN
      IF DBMS_LOB.FILEEXISTS (v_file) = 1 THEN
      DBMS_OUTPUT.PUT_LINE ('File exists.');

      Begin
      EXECUTE IMMEDIATE
                ' SELECT NULL '||
                ' FROM EXT_ABC' ||
                ' WHERE ROWNUM = 1 '
                INTO l_dummy;
      EXCEPTION
      WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE (SUBSTR(SQLERRM(SQLCODE),1,200));
             DBMS_OUTPUT.PUT_LINE (SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,1,200));
      End;
      ELSIF DBMS_LOB.FILEEXISTS (v_file) = 0 THEN
      DBMS_OUTPUT.PUT_LINE ('File does not exist');
      ELSE
      DBMS_OUTPUT.PUT_LINE ('Unable to test existence');
      END IF;
      END;

       

      From Node 1 it gives me below error. Whereas when I try to read the external table from Node 2 I am able to access it.

       

      ORA-29913: error in executing ODCIEXTTABLEOPEN callout

      ORA-29400: data cartridge error

      KUP-11010: unable to open at least one dump file for load


      Request Help to sort this issue.