The DATAPUMP command builds PL/SQL that uses the DBMS_DATAPUMP package to execute exports and imports.

• Preview the PL/SQL and Parameters

  • Use the -noexec option to validate the export or import. This option shows the generated PL/SQL and parameters but does not submit the job for execution.

• Get additional information about the command processing

  • Use the -verbose option to see additional diagnostic information.

  • This option also provides queries that you can copy to view the last line of the log and percentage completed.

• View the job log

  • When using the -wait option, the log is written to the console.

  • When using -wait false, you can copy the log to object storage if you cannot see files in the directory.

  • When using -wait false, you can view the log file with the following code block for Oracle Database release 12.2 and later versions:

    var c clob;
    set long 32000
    begin select to_clob(BFILENAME('DATA_PUMP_DIR','ESQL_<n>.LOG')) into :c from dual; end;
    /
    print c
    

• When importing or exporting as the logged-in user, it is recommended not to use the SYS role.

• Inserting a row into a table confirms you have quota on the tablespace. If you do not have quota, you see the following error after you run a DATAPUMP command: ORA-31626: job does not exist.

  The following code snippet illustrates this problem:

  *** Error with DataPump command 
  
  grant connect, resource, create session to user1 identified by user1;
  grant read on directory DATA_PUMP_DIR to user1;
  grant write on directory DATA_PUMP_DIR to user1;
  
  SQL> connect <db-connect-string>
  Connected
  
  SQL> datapump export -schemas user1
  
  Datapump Command Start ** at 2022.03.10-15.51.28
  Initiating DATA PUMP
  DATABASE TIME ZONE: VERSION:32 CON_ID:0
  Log
  Location: DATA_PUMP_DIR:ESQL_1614.LOG
  ORA-31626: job does not exist
  ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
  ORA-06512: at "SYS.DBMS_DATAPUMP", line 1849
  ORA-06512: at "SYS.DBMS_DATAPUMP", line 6869
  ORA-06512: at line 25
   
  ** Datapump Command End ** at 2022.03.10-15.51.30
  SQL> exit
  
  ***Resolve Error by Granting Tablespace Quota to User
  Connected to:
  Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  Version 19.3.0.0.0
   
  SQL> alter user user1 quota 128M on users;
  Grant succeeded.
   
  SQL> connect <db-connect-string>
  Connected.
  
  SQL> datapump export -schemas user1
  
  ** Datapump Command Start ** at 2022.03.10-15.54.15
  Initiating DATA PUMP
  DATABASE TIME ZONE: VERSION:32 CON_ID:0
  Log Location: DATA_PUMP_DIR:ESQL_1616.LOG
  Starting "USER1"."ESQL_1616":
  Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  Processing object type SCHEMA_EXPORT/TABLE/TABLE
  Processing object type SCHEMA_EXPORT/TABLE/COMMENT
  Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
  Master table "USER1"."ESQL_1616" successfully loaded/unloaded
  ******************************************************************************
  Dump file set for USER1.ESQL_1616 is:
    /opt/oracle/admin/DB193C/dpdump/D9C3824B6E651CA4E053020014C3358C/ESQL_1616.DMP
  Job "USER1"."ESQL_1616" successfully completed at Thu Mar 10 15:54:52 2022 elapsed 0 00:00:33
  DataPump Operation Status 'COMPLETED'
  Jobname = ESQL_1616
  ** Datapump Command End ** at 2022.03.10-15.54.56