Impdp gets stuck, DW waits for librarycachelock
My colleagues reported that impdp was stuck in the SCHEMA_REPORT/TYPE/TYPE_SPEC step and did not respond after more than one hour,
Check the v $ session:
Select program, sid, event, blocking_session from gv $ session where program like '% DW % ';
Result:
DW01, 98, library cache lock, 213
DW03, 13, library cache lock, 213
DW02, 36, library cache lock, 213
DW00, 213, library cache lock, 213
All DW processes are waiting for the library cache lock. Check the impdp parameter before:
Impdp u/p dumpfile = f. dmp schemas = a remap_schema = a: B remap_tablespace = a: B TABLE_EXISTS_ACTION = REPLACE transform = oid: n
Originally, it was terminated in the middle of impdp, so the TABLE_EXISTS_ACTION = REPLACE option was used in impdp again, but the problem was that when a TYPE was created,
Create or replace type "O_INDO" as OBJECT
(
CODE_ID varchar2 (400)
);
Another TYPE O_INDO_TABLE depends on this O_INDO, so it cannot replace this O_INDO. All DW sessions are waiting for the library cache lock, and the session blocks itself, forming a deadlock.
Solution:
Drop schema B and execute impdp again.