Datapump ORA-31626 ORA-31638 ORA-31632 ORA-00942

Datapump expdp finished, but generated errors :

ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 405
ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user TETSUSER
ORA-31632: master table "TESTUSER.SYS_EXPORT_SCHEMA_01" not found, 
           invalid, or inaccessible
ORA-00942: table or view does not exist
SQL> SELECT owner_name, job_name, operation, job_mode,
            state, attached_sessions
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE STATE      ATTACHED_SESSIONS
---------- -------------------- ---------- -------- ---------- -----
TESTUSER      SYS_EXPORT_SCHEMA_01  EXPORT    SCHEMA   COMPLETING 0

Subscribe to get access

Read more of this content when you subscribe today.

How to Monitor DataPump

A couple of ways to monitor DataPump job

Get DataPump job details from database

SQL>select JOB_NAME,OPERATION,JOB_MODE,DEGREE,
           ATTACHED_SESSIONS,STATE 
     from dba_datapump_jobs

JOB_NAME             OPERATION JOB_MODE DEGREE ATTAC..TIONS STATE
-------------------- --------- -------- ------ ------------ ------
SYS_EXPORT_SCHEMA_01 EXPORT    SCHEMA   4       1          EXECUTING

Find job name from the DataPump log file

...
..
.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": directory= ..... PARALLEL=4

Query V$SESSION_LONGOPS

SQL> select inst_id, username,target_desc,sofar,
            totalwork,TIME_REMAINING 
      from gV$SESSION_LONGOPS 
     where TIME_REMAINING>1000;

INST_ID USERNAME TARGET_DESC SOFAR   TOTALWORK   TIME_REMAINING
------- -------- ----------- ------- ----------- --------------
 4      SYS                  248779  4294967296  28121732
 3      SYS                  467021  4294967296  15108237
 3      SYS      EXPORT      8824    21597       2335
 2      SYS                  313184  4294967296  22351992

Attach to the running DataPump job

$ expdp ATTACH=SYS_EXPORT_SCHEMA_01

Export> help
...
..
.
 ------------------------------------------------------------------------------
HELP
 Summarize interactive commands.

KILL_JOB
 Detach and delete job.

PARALLEL
 Change the number of active workers for current job.

REUSE_DUMPFILES
 Overwrite destination dump file if it exists [NO].

START_JOB
 Start or resume current job.
 Valid keyword values are: SKIP_CURRENT.

STATUS
 Frequency (secs) job status is to be monitored where
 the default [0] will show new status when available.

STOP_JOB
 Orderly shutdown of job execution and exits the client.
 Valid keyword values are: IMMEDIATE.

$status
...
..
.

Kill the running DataPump job

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes