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-02085: database link DB_LINK connects to SOURCE_DB

After successfully created the database link called “DB_LINK”, the try to access remote table with the following errors:

SQL>  select  count(*) from remote_user.table_name@db_link;
 select  count(*) from REMOTE_USER.TABLE_NAME@DB_LINK                                                         *
ERROR at line 1:
ORA-02085: database link DB_LINK connects to SOURCE_DB
 $ oerr ora 02085
02085, 00000, "database link %s connects to %s"
// *Cause: a database link connected to a database with a different name.
//  The connection is rejected.
// *Action: create a database link with the same name as the database it
//  connects to, or set global_names=false.
//
SQL>  show parameter global_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
global_names                         boolean     TRUE

SOLUTION

1)Reset parameter global_names to FALSE, and recreate the database link again with any name you want.

SQL> alter system set global_names=false;

SQL> Create public database link DB_link connect to ....

SQL>  select  count(*) from remote_user.table_name@db_link;

  COUNT(*)
----------
      1000

OR

2) Create database link with the same name as source database / PDB GLOBAL_NAME.

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------------
SOURCE_DB

SQL> Create public database link SOURCE_DB connect to ....

SQL>  select  count(*) from remote_user.table_name@source_db;

  COUNT(*)
----------
      1000

For connecting to PDB, you need be in the PDB to get right GLOBAL_NAME:

SQL> show con_name;

CON_NAME
------------------------------
SOURCEDBPDB

SQL>  select * from global_name;

GLOBAL_NAME
--------------------------------------------
SOURCEDBPDB

SQL>Create public database link SOURCEDBPDB connect to ....

SQL>  select  count(*) from remote_user.table_name@sourcedbpdb;

  COUNT(*)
----------
      1000