Data Pump Import Job is Hanging at SCHEMA_EXPORT/STATISTICS/MARKER

In 12.1.0.2 data pump impdp job is hanging like below:

...
..
.
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Query the database, the following sql is running :

insert into sys.spd_scratch_tab l (n1)           
   select to_number(c5) 
   from  sys."_user_stat_varray" d 
   where type = 'D' 
   and not exists  (select null 
                      from  sys."_user_stat_varray" do      
                     where do.type = 'O' 
                       and do.c5 = d.c5
                       and not exists  (select null 
                                         from sys.spd_scratch_tab l            
                                        where l.c1 = do.c1 
                                        and l.c2 = do.c2)
                   )

CAUSE

It is a known issue in 12.1.0.2

SOLUTION

1) Upgrade to 12.2.0.1 or higher.

OR

2) Download and apply the Generic Platform Patch:20345554.

OR

3) Workaround is to exclude statistics during the import using EXCLUDE=STATISTICS .

expdp did not create dumpfile while expdp is successful

A client sent the following information about the dump file of expdp is unavailable, while the datapump log shows expdp completes successfully.

$ expdp exp_user/password@testdbpdb directory=DATA_PUMP_DIR dumpfile=hr.dmp schemas=hr reuse_dumpfiles=yes logfile=exp_hr.log
..
..
.
Master table "EXP_USER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EXP_USER.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/hr.dmp
Job "EXP_USER"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 27 11:53:13 2021 elapsed 0 00:00:57

$ ls -ltr /home/oracle/hr.dmp
ls: cannot access /home/oracle/hr.dmp: No such file or directory

CAUSE and SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

ORA-31633: unable to create master table SYS_EXPORT_SCHEMA_01

The following errors occur when exporting/importing through data pump:

 ORA-31626: job does not exist
 ORA-31633: unable to create master table "USERNAME.SYS_EXPORT_SCHEMA_01"
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
 ORA-06512: at "SYS.KUPV$FT", line 1048
 ORA-01031: insufficient privileges

SOLUTION

To make sure user ‘USERNAME’ to have enough privileges to do import/export.

DataPump: UDE-00008 ORA-31626 ORA-39086

The following errors occur when exporting data by using DataPump:

UDE-00008: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
ORA-06512: at line 1

Check the log which shows exporting has been successful.

...
..
.
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
C:\TEMP\TEST.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:08:38

So it is a bug, and this error can be ignored.