Solution to impdp exceptions and interruptions

Check the database load today and find that the cpu consumption is abnormally high. There are many dw processes, but the impdp process does not exist. Check the process of datapump and find that the process is large.

Check the database load today and find that the cpu consumption is abnormally high. There are many dw processes, but the impdp process does not exist. Check the process of datapump and find that the process is large.

Check the database load today and find that the cpu consumption is abnormally high. There are many dw processes, but the impdp process does not exist.
Check the process of datapump and find a large number of jobs, but the status is not running.
Select * from dba_datapump_jobs where owner_name = 'Mig _ test ';
SYS_IMPORT_TABLE_01
SYS_IMPORT_TABLE_02
SYS_IMPORT_TABLE_03
SYS_IMPORT_TABLE_04
SYS_IMPORT_TABLE_05
SYS_IMPORT_TABLE_06
SYS_IMPORT_TABLE_07
SYS_IMPORT_TABLE_08
SYS_IMPORT_TABLE_09
SYS_IMPORT_TABLE_10
SYS_IMPORT_TABLE_12
SYS_IMPORT_TABLE_13
SYS_IMPORT_TABLE_14
SYS_IMPORT_TABLE_15
SYS_IMPORT_TABLE_16
SYS_IMPORT_TABLE_17
SYS_IMPORT_TABLE_18
SYS_IMPORT_TABLE_20
SYS_IMPORT_TABLE_21
SYS_IMPORT_TABLE_23
SYS_IMPORT_TABLE_24
SYS_IMPORT_TABLE_25
SYS_IMPORT_TABLE_26
SYS_IMPORT_TABLE_28

....
Check the database session information and find that there are more than 120 sessions under a temporary test user. This environment is usually used by no one, so there won't be so many sessions.
There is no impdp task. Check the temporary user and find that there are many SYS_IMPORT_TABLE_xx tables, ETxxx and ERRxxx tables. These tables are temporary tables created by datapump during data import.
The ERR table is similar to the following format.
ERR $ DP13FC20810001
ERR $ DP16F10AF10001
ERR $ DP1AA200C30001
ERR $ DP1C125B790001
ERR $ DP1C1B86AF0001

The ET table is similar to the following format.
ET $000D0F650001
ET $00C11B410001
ET $024502130001
ET $02FBAF530001
ET $02FE249B0001
ET $03B551550001
ET $03BE47230001
ET $05312FFF0001
ET $05380F350001
ET $05F70DAD0001

Key Points of migrating 11G data to 10g platform through expdp & impdp

Oracle Data Pump examples and some precautions (expdp/impdp)

Oracle datapump expdp/impdp hang

Expdp/impdp for Oracle 10g to 11g data migration

Viewing the content is also forbidden.
SQL> select * from ET $1B5C6DCF0001;
ERROR:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump
Job.

Since there is no corresponding impdp task to run, those sessions are also empty.
I confirmed with my colleagues that the data task in this test was executed for too long and the process was deleted. I did not expect to kill the impdp process. The corresponding parallel process still exists,
A partition table is divided into 100 parts, each containing some partition data. In this way, there will be about 120 sessions in parallel,
The scripts they use are similar to the following.
Nohup impdp xxxx/xxxx directory = memo_dir dumpfile = export parallel = 2 include = table_data logfile = export parfile = export TABLE_EXISTS_ACTION = append REMAP_SCHEMA = prdappo: MIG_TEST DATA_OPTIONS = upload & amp &
Nohup impdp xxxx/xxxx directory = memo_dir dumpfile = export parallel = 2 include = table_data logfile = export parfile = export TABLE_EXISTS_ACTION = append REMAP_SCHEMA = prdappo: MIG_TEST DATA_OPTIONS = upload & amp &
Nohup impdp xxxx/xxxx directory = memo_dir dumpfile = export parallel = 2 include = table_data logfile = export parfile = export TABLE_EXISTS_ACTION = append REMAP_SCHEMA = prdappo: MIG_TEST DATA_OPTIONS = upload & amp &
Nohup impdp xxxx/xxxx directory = memo_dir dumpfile = export parallel = 2 include = table_data logfile = export parfile = export TABLE_EXISTS_ACTION = append REMAP_SCHEMA = prdappo: MIG_TEST DATA_OPTIONS = upload & amp &
Nohup impdp xxxx/xxxx directory = memo_dir dumpfile = export parallel = 2 include = table_data logfile = export parfile = export TABLE_EXISTS_ACTION = append REMAP_SCHEMA = prdappo: MIG_TEST DATA_OPTIONS = upload & amp &

Confirm and delete the session. You can use the following SQL statement to find the corresponding session and delete it after confirmation.
Select 'alter system kill session '| chr (39) | sid |', '| serial # | chr (39) | '; 'From v $ session where username = 'Mig _ test ';

For more details, please continue to read the highlights on the next page: