5 Replies Latest reply on Jul 27, 2020 9:38 AM by user545194

    Datapump EXP error

    user545194

      Our Env: Oracle Database 19c SE2 on Oracle Linux

       

      We would like to export a schema via datapump api, but when submitting the job this error message appears; "Table or View does not exist".

      Compression is off.

       

      The schema has been granted the following roles/privs:

       

      EXP_FULL_DATABASE

      IMP_FULL_DATABASE

      DBA

      connect

      resource

      read,write on data_pump_dir

      unlimited quota on own tablespace

      create session

      create table

       

      What could be the cause?

        • 1. Re: Datapump EXP error
          EdStevens

          user545194 wrote:

           

          Our Env: Oracle Database 19c SE2 on Oracle Linux

           

          We would like to export a schema via datapump api, but when submitting the job this error message appears; "Table or View does not exist".

          Compression is off.

           

          The schema has been granted the following roles/privs:

           

          EXP_FULL_DATABASE

          IMP_FULL_DATABASE

          DBA

          connect

          resource

          read,write on data_pump_dir

          unlimited quota on own tablespace

          create session

          create table

           

          What could be the cause?

          Don't you think it might be helpful if you showed the actual command, and parm file if applicable?

          I don't know about you, but I've never been able to debug something I cannot see.

          • 2. Re: Datapump EXP error
            user545194

            Here is the plsql block that the datapump api generated:

             

            set scan off

            set serveroutput on

            set escape off

            whenever sqlerror exit

            DECLARE

                h1 number;

                s varchar2(1000):=NULL;

                errorvarchar varchar2(100):= 'ERROR';

                tryGetStatus number := 0;

                success_with_info EXCEPTION;

                PRAGMA EXCEPTION_INIT(success_with_info, -31627);

            begin

                h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'TABLE', job_name => 'EXP_SD_102-10_30_43', version => 'COMPATIBLE');

                tryGetStatus := 1;

                dbms_datapump.set_parallel(handle => h1, degree => 1);

                dbms_datapump.add_file(handle => h1, filename => 'EXP_SCHEMA_220720-'||to_char(sysdate,'hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);

                dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);

                dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''MY_SCHEMA'')');

                dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''MY_TABLE'')');

                dbms_datapump.add_file(handle => h1, filename => 'EXPDAT%U-'||to_char(sysdate,'hh24_mi_ss')||'.DMP', directory => 'DATA_PUMP_DIR', filesize => '500M',  filetype => 1);

                dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);

                dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');

                dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');

                dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);

                dbms_datapump.detach(handle => h1);

                errorvarchar := 'NO_ERROR';

            EXCEPTION

                WHEN OTHERS THEN

                BEGIN

                    IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN

                        DBMS_DATAPUMP.DETACH(h1);

                    END IF;

                EXCEPTION

                WHEN OTHERS THEN

                    NULL;

                END;

                RAISE;

            END;

            /

             

            The error message:

             

            00942. 00000 -  "table or view does not exist"

            *Cause:   

            *Action:

             

            Not much to work with.

            • 3. Re: Datapump EXP error
              EdStevens

              user545194 wrote:

               

              Here is the plsql block that the datapump api generated:

               

              set scan off

              set serveroutput on

              set escape off

              whenever sqlerror exit

              DECLARE

              h1 number;

              s varchar2(1000):=NULL;

              errorvarchar varchar2(100):= 'ERROR';

              tryGetStatus number := 0;

              success_with_info EXCEPTION;

              PRAGMA EXCEPTION_INIT(success_with_info, -31627);

              begin

              h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'TABLE', job_name => 'EXP_SD_102-10_30_43', version => 'COMPATIBLE');

              tryGetStatus := 1;

              dbms_datapump.set_parallel(handle => h1, degree => 1);

              dbms_datapump.add_file(handle => h1, filename => 'EXP_SCHEMA_220720-'||to_char(sysdate,'hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);

              dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);

              dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''MY_SCHEMA'')');

              dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''MY_TABLE'')');

              dbms_datapump.add_file(handle => h1, filename => 'EXPDAT%U-'||to_char(sysdate,'hh24_mi_ss')||'.DMP', directory => 'DATA_PUMP_DIR', filesize => '500M', filetype => 1);

              dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);

              dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');

              dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');

              dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);

              dbms_datapump.detach(handle => h1);

              errorvarchar := 'NO_ERROR';

              EXCEPTION

              WHEN OTHERS THEN

              BEGIN

              IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN

              DBMS_DATAPUMP.DETACH(h1);

              END IF;

              EXCEPTION

              WHEN OTHERS THEN

              NULL;

              END;

              RAISE;

              END;

              /

               

              The error message:

               

              00942. 00000 - "table or view does not exist"

              *Cause:

              *Action:

               

              Not much to work with.

              Well, your reported error was "Table or View does not exist".

              And I see

              dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''MY_SCHEMA'')');

              dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''MY_TABLE'')');

              So, does MY_SCHEMA.MY_TABLE exist?

               

              I've only ever worked with command-line.  But you say you've shown what the api generated, so I'd want to see the code YOU used to call the api.

              • 4. Re: Datapump EXP error
                Dean Gagne-Oracle

                I agree with Ed, It looks like my_schema.my_table mat not exist.   Some things to think about...  are you logged into the right database.  I don't see the connect string so maybe that is missing, especially if you log into one database but use a link to export from a remote database.   I also only use the command line interface, so reading the api script looks right, but I never remember all of the switches that I turn on the may be missing.

                 

                Can you post the command line export command?

                 

                Thanks

                 

                Dean

                • 5. Re: Datapump EXP error
                  user545194

                  Yes, my_schema and my_table exist in the database. No, there are no db links involved here. Strange thing, the export succeeded for another schema

                  via api. I usually use system and the cli for data movement, but in this case we neither have the system role/privilege nor the database utilities installed on

                  the client machines. We only have SQL Developer.