5 Replies Latest reply on Jan 18, 2018 7:27 PM by user1297334

    SQL Developer - ORA-31626: job does not exist

    user1297334

      I am having a very frustrating problem.  We are moving from 11.2.0.1 to 12.2.0.1 and I am using data pump to move the data.

       

      A few details:

      The 11g and 12c databases are on different servers

      11g server is running Solaris 10 on Sparc

      12c server is Solaris 11 on Sparc

      Using SQL developer version 17.4.0.355

       

      I have created the export files (by schema) from 11g using SQL Developer and moved the files to the data pump directory on the new server.

       

      When I try to import a schema using the data pump import wizard of SQL Developer I get ORA-31626: job does not exist.   There are no other jobs running.

       

      I was able to import the data at the command line using impdp.

       

      Any idea why I would get the error in SQL Developer, but not at the command line?

       

      Thanks!

        • 1. Re: SQL Developer - ORA-31626: job does not exist
          thatJeffSmith-Oracle

          >>When I try to import a schema using the data pump import wizard of SQL Developer I get ORA-31626: job does not exist.   There are no other jobs running.

          Need more info.

           

          When you use the wizard, it generates a PL/SQL block. What does that look like. If you run it in a sql worksheet, do you get any errors?

          • 2. Re: SQL Developer - ORA-31626: job does not exist
            user1297334

            The wizard isn't getting that far.   I get the ORA-31626 error as soon as I hit the 'next' button on the 'Type' window of the wizard.

             

             

            .import.JPG

            • 3. Re: SQL Developer - ORA-31626: job does not exist
              thatJeffSmith-Oracle

              We run this script on that screen...based on the job name you specify in the first screen.

               

              DECLARE

                  h1 number;

                  c number;

                  h1got boolean := false;

                  no_such_job EXCEPTION;

                  PRAGMA EXCEPTION_INIT(no_such_job, -31626);

              BEGIN

                  h1:=DBMS_DATAPUMP.ATTACH(:1, USER);

                  h1got := true;

                  DBMS_DATAPUMP.STOP_JOB(h1,1,0,0);

                   BEGIN

                       dbms_datapump.detach(handle => h1);

                   EXCEPTION

                        when dbms_datapump.INVALID_HANDLE then null;

                        when dbms_datapump.SUCCESS_WITH_INFO then null;

                        when dbms_datapump.NO_SUCH_JOB then null;

                   END;

              EXCEPTION WHEN NO_SUCH_JOB THEN NULL;

              WHEN OTHERS THEN

                  BEGIN

                      IF (h1got=true) THEN

                          DBMS_DATAPUMP.DETACH(h1);

                      END IF;

                  EXCEPTION

                  WHEN OTHERS THEN NULL;

                  END;

                  RAISE;

              END;

              • 4. Re: SQL Developer - ORA-31626: job does not exist

                            DBMS_DATAPUMP.DETACH(h1);

                And that statement can throw the exception OP posted:

                https://docs.oracle.com/database/121/ARPLS/d_datpmp.htm#ARPLS66036

                Exceptions

                 

                    INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

                 

                    SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

                 

                    NO_SUCH_JOB. The specified job does not exist.

                Maybe the handle was never initialized to begin with?

                • 5. Re: SQL Developer - ORA-31626: job does not exist
                  user1297334

                  ok, so I tried an export job and got the same error:

                   

                  ORA-31626: job does not exist

                  ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

                  ORA-06512: at "SYS.DBMS_DATAPUMP", line 1852

                  ORA-06512: at "SYS.DBMS_DATAPUMP", line 6833

                  ORA-06512: at line 9

                   

                   

                  But I was able to get far enough into the SQL Developer export wizard that it generated the script:

                   

                  set scan off

                  set serveroutput on

                  set escape off

                  whenever sqlerror exit

                  DECLARE

                      h1 number;

                      s varchar2(1000);

                      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 => 'SCHEMA', job_name => 'EXPORT_JOB_SQLDEV_1144', version => 'COMPATIBLE');

                      tryGetStatus := 1;

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

                      dbms_datapump.add_file(handle => h1, filename => 'EXPDAT-ews.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(''EWS'')');

                      dbms_datapump.add_file(handle => h1, filename => 'EXPDAT%U.DMP', directory => 'DATA_PUMP_DIR', filesize => '100M',  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;